UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Invalid Use Of Null, Access 2016    
 
   
cheekybuddha
post Sep 19 2019, 01:19 PM
Post#41


UtterAccess VIP
Posts: 11,533
Joined: 6-December 03
From: Telegraph Hill


Well, certainly an alternative way of going about it!

I'm sorry, no chance to look at it today at work.

But I'm pleased you have found a solution! yayhandclap.gif

Continued success, Mike!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Sep 19 2019, 02:28 PM
Post#42



Posts: 6,123
Joined: 11-November 10
From: SoCal, USA


hi Mike, i downloaded your test db, and ran it in A2016 64-bit on Win7Pro. i put a break in the code immediately after the Debug.Print line, then copied the SQL statement from the Immediate window, pasted it into the SQL pane of a new query, and changed to DatasheetView. i got the following error msg, as

Attached File  Capture.PNG ( 30K )Number of downloads: 0


see the red dots where Access doesn't like the fieldname, probably because it begins with a number instead of an alpha character. so i put both instances of the fieldname in brackets, in the SQL statement in VBA, as

QUOTE
30 strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledNsg, NursingCareNeedsNotes, " _
& " TherapyCareNeedsNotes, SkilledOT, OTProjectedSched , SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, PriorSetting , EstimatedTimeFrame, " _
& " [100thDayDateActual], LastCoveredDay, DischargePlan, DischargeLocation, AreasofFocusConcern, CareConferences) " _
& " SELECT cr.Resident_FK, cr.ReviewDate, " _
& " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledNsg, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.SkilledOT, cr.OTProjectedSched , " _
& " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.PriorSetting, cr.EstimatedTimeFrame, cr.[100thDayDateActual], cr.LastCoveredDay, " _
& " cr.DischargePlan, cr.DischargeLocation, cr.AreasofFocusConcern, cr.CareConferences " _
& " FROM tblCareReviews cr " _
& " WHERE CareReviewsID = " & Me.CareReviewsID & ";"

then i removed the break from the code and ran it again by just clicking the button on the form. it worked without error.

i know you used an alternative method to move forward, hon; just thought you might be interested in the result of my test. :)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Sep 19 2019, 02:31 PM
Post#43


UtterAccess VIP
Posts: 13,419
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

Many thanks for the update.

I will test your solution and get back to you

pompom.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Phil_cattivocara...
post Sep 19 2019, 02:33 PM
Post#44



Posts: 363
Joined: 2-April 18



Tina_t got the same than me. Your test must pass.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Sep 19 2019, 02:37 PM
Post#45


UtterAccess VIP
Posts: 13,419
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

Many thanks just works a treat.

Thanks again to everyone.

kisses.gif pompom.gif
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Sep 19 2019, 03:10 PM
Post#46



Posts: 6,123
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon, it's nice to finally be able to help someone using A2016! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post Sep 20 2019, 04:18 AM
Post#47


UtterAccess VIP
Posts: 11,533
Joined: 6-December 03
From: Telegraph Hill


yayhandclap.gif yayhandclap.gif Double-Yay! yayhandclap.gif yayhandclap.gif

uarulez2.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
PatHartman
post Sep 21 2019, 10:18 AM
Post#48



Posts: 64
Joined: 20-February 03
From: Stratford,Ct USA


Just so you know, your original problem was caused because you were copying form fields to variables and then inserting the variables in the SQL string. This was an unnecessary step. You could have just referenced the form fields in the insert statement. The problem was caused because at least one of the string fields was occasionally null and string variables CANNOT be Null. Another way to resolve the problem would have been to define the variables as Variant rather than string. But simply omitting the "copy to a variable" step would have eliminated the problem.
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 09:39 PM