NULL DATE

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

This works fine if there is a date in the textbox, but if the textbox is
empty or null, I get an error. This is part of an SQL statement. What can
I do if it's Null so that it still works.
Thanks
DS

Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")
 
Change you line to

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"))

This will return a zero legnth string instead of a null value, and will
allow your code to process it.

Note that if at any point you are comparing that field to Null, it will
fail, and you will have to compare it against "" instead.
 
I tried this, doesn't work.
Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),
"Null")
DS
 
Remove Null and leave just the two quotes.

If that doesn't work, let me know what error you are getting.
 
Thanks, I'm receiving an Error message 3134. Here it is in its entirity.
DSQL = "INSERT INTO tblDiscounts " & _
"(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)
" & _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," &
Forms!frmBSDiscountNames![ChkExp] & ", " & _
"" & Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#")) &
", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)

DS
 
This doesn't work either.

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),"Null")
AS DiscountExpDate

DS
 
INSERT ERROR 3134
Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"), "")
Thanks
DS
 
Try:

Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"")
 
I didn't see your post with the code before I replied. Here is my
recommendation with the update placed in your code.


Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"")


DSQL = "INSERT INTO tblDiscounts " &
"(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)" & _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," &
Forms!frmBSDiscountNames![ChkExp] & ", " & _
DiscountExpDate & ", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)
 
Hi Thanks,
This I'm a little confused on. The Dim I know where to put, But what goes
in the SQL statement.
Thanks
DS
 
OK, I tried it, Works fine with a date but without a date I get an Insert
message 3134.
Thanks
DS
 
To be honest, SQL statements aren't my strong point. If you are appending
data on your form to a table, you could use the rst commands. That's what I
use most of the time. I'll look into your SQL string and get back to you
shortly.
 
No. Dates are numeric, so you can't use "" as a possible value. Not only
that, but the Nz function isn't appropriate as written: Format returns a
string, so there's no point in using Nz on the result of the function.

What's needed is

DiscountExpDate = IIf(IsNull(Forms!frmBSDiscountNames![TxtExpDate]), "Null",
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#"))

or, easier,

DiscountExpDate =
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#;;;\N\u\l\l"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Scott Whetsell, A.S. - WVSP"
I didn't see your post with the code before I replied. Here is my
recommendation with the update placed in your code.


Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"")


DSQL = "INSERT INTO tblDiscounts " & _
"(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)"
& _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," &
Forms!frmBSDiscountNames![ChkExp] & ", " & _
DiscountExpDate & ", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)



DS said:
This doesn't work either.

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"Null")
AS DiscountExpDate

DS
Click to expand...
Click to expand...
 
Excellent point Doug, didn't even think of that yet. DS let us know if it
works. Also check your SQL string and make sure that you aren't trying to
write to an AutoNumber field.

Douglas J. Steele said:
No. Dates are numeric, so you can't use "" as a possible value. Not only
that, but the Nz function isn't appropriate as written: Format returns a
string, so there's no point in using Nz on the result of the function.

What's needed is

DiscountExpDate = IIf(IsNull(Forms!frmBSDiscountNames![TxtExpDate]), "Null",
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#"))

or, easier,

DiscountExpDate =
Format(Forms!frmBSDiscountNames![TxtExpDate],"\#mm\/dd\/yyyy\#;;;\N\u\l\l"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Scott Whetsell, A.S. - WVSP"
I didn't see your post with the code before I replied. Here is my
recommendation with the update placed in your code.


Dim DiscountExpDate As String
DiscountExpDate = Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"")


DSQL = "INSERT INTO tblDiscounts " & _
"(DiscountID,DiscountName,DiscountDP,DiscountAmount,DiscountOpen,DiscountActive,DiscountExpire,DiscountExpDate,DiscountAction,DiscountTypeID,DiscountWhere,DiscountReportID)"
& _
"Values(" & Forms!frmBSDiscountNames!TxtID & ", " & _
"" & Chr(34) & Forms!frmBSDiscountNames!TxtName & Chr(34) & ", " & _
"'" & 1 & "', " & _
"'" & Forms!frmBSDiscountNames!TxtAmount & "', " & _
"" & Forms!frmBSDiscountNames![ChkOpen] & ", " & _
"" & Forms!frmBSDiscountNames![ChkActive] & "," &
Forms!frmBSDiscountNames![ChkExp] & ", " & _
DiscountExpDate & ", " & _
"'" & Forms!frmBSDiscountNames!TxtDiscountAction & "', " & _
"" & Forms!frmBSDiscountNames!TxtDiscountTypeID & ",'" &
Forms!frmBSDiscountNames!TxtDiscountWhere & "','" &
Forms!frmBSDiscountNames!TxtReportID & "')"
DoCmd.RunSQL (DSQL)



DS said:
This doesn't work either.

Nz(Format(Forms!frmBSDiscountNames![TxtExpDate],
"\#mm\/dd\/yyyy\#"),"Null")
AS DiscountExpDate

DS
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas, AGAIN! I see the error of my ways. I used your statement in
my SQL statement and it works great. Once again I appreciate the help!
DS
 
Thanks Scott I appreciate your help. I used Dougs suggestion and I am up
and running.
Once again.
Thank You.
DS
 
Back
Top