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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert Into Fails With Null Value, Access 2016    
 
   
ordnance1
post Oct 11 2018, 06:36 PM
Post#1



Posts: 555
Joined: 7-May 11



I am creating an Insert Into statement but am having a problem when trying to insert a Null value.

A field I need to insert into is MtgDay_AerieAux but often times the value I am passing " & Me.cbo_Aerie_MtgDay & " is null. This causes the Insert statement to fail. Is there a workaround for this problem?

CODE
strSQL2 = "INSERT INTO tbl_Aeries_Auxiliaries (Aeries_Auxiliaries_Type_ID, ClubNum, Suspened_Date, 1_st_Week_AerieAux, 2_nd_Week_AerieAux,3_rd_Week_AerieAux, 4_th_Week_AerieAux, MtgDay_AerieAux) " & _
        "VALUES (1, " & Me.txt_ClubNum & ", #" & Format(Me.txt_Suspened_Date_Aerie, "mm-dd-yyyy") & "#, " & Me.chk_1st_Aerie & ", " & Me.chk_2nd_Aerie & ", " & Me.chk_3rd_Aerie & ", " & Me.chk_4th_Aerie & ", " & Me.cbo_Aerie_MtgDay & ");"
        DoCmd.RunSQL strSQ
Go to the top of the page
 
theDBguy
post Oct 11 2018, 07:11 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Try wrapping it with the Nz() function. For example,

Nz(Me.cbo_Aerie_MtgDay, “Null”)

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Oct 11 2018, 07:28 PM
Post#3



Posts: 555
Joined: 7-May 11



I have reduced thing to the one field but I still get an error Syntax error i INSERT INTO statement, Run-time error 3134

CODE
strSQL2 = "INSERT INTO tbl_Aeries_Auxiliaries (MtgDay_AerieAux) " & _
        "VALUES (" & Nz(Me.cbo_Aerie_MtgDay, "Null") & ");"
        DoCmd.RunSQL strSQ
Go to the top of the page
 
theDBguy
post Oct 11 2018, 09:21 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Do you have Option Explicit on? Unless it's just a typo, you assigned the SQL statement to a variable named strSQL2 but then tried to run RunSQL against a variable named strSQ.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Oct 11 2018, 09:44 PM
Post#5



Posts: 555
Joined: 7-May 11



I have Option Compare Database and Dim strSQL2 As String
Go to the top of the page
 
theDBguy
post Oct 11 2018, 09:51 PM
Post#6


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Try adding Option Explicit also and then try to Compile your database by going to Debug > Compile.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Oct 12 2018, 08:14 AM
Post#7



Posts: 555
Joined: 7-May 11



Thank you Sir!
Go to the top of the page
 
ordnance1
post Oct 12 2018, 10:02 AM
Post#8



Posts: 555
Joined: 7-May 11



A follow-up question if I could:

I also have a time field that may or may not have a value. I have tried many variants of the code below with no success. It works fine if there is a time in Me.txt_Aerie_MeetingTime but fails if there is no time.


CODE
strSQL2 = "INSERT INTO tbl_Aeries_Auxiliaries (MtgTime_AerieAux) " & _
        "VALUES (#" & IIf(IsNull(Me.txt_Aerie_MeetingTime), Null, Format(Me.txt_Aerie_MeetingTime, "Short Time")) & "#);"
        DoCmd.RunSQL strSQL2
Go to the top of the page
 
theDBguy
post Oct 12 2018, 10:17 AM
Post#9


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

How did you fix the first problem? Did you enclose Null in quotes? If so, try it again this time.

Also, move the hash marks "#" next to the Format() function.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Oct 12 2018, 11:31 AM
Post#10



Posts: 555
Joined: 7-May 11



Thanks,

I have tried Null both with and without quotes.
Go to the top of the page
 
theDBguy
post Oct 12 2018, 11:35 AM
Post#11


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


It has to be with quotes. Didn't you fix the first problem using quotes? Can you show us what you tried? Thanks.


--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ordnance1
post Oct 12 2018, 02:04 PM
Post#12



Posts: 555
Joined: 7-May 11



Here was the final solution:

CODE
" & IIf(Nz(Me.txt_MTGTime_Trustees, 0) = 0, "Null", "#" & Format(Me.txt_MTGTime_Trustees, "Short Time") & "#") & "
Go to the top of the page
 
theDBguy
post Oct 12 2018, 02:05 PM
Post#13


Access Wiki and Forums Moderator
Posts: 73,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 06:29 PM