NULL DATE

[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\#")

 

[Scott Whetsell, A.S. - WVSP]

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.

 

[DS]

I tried this, doesn't work.
Nz(Format(Forms!frmBSDiscountNames![TxtExpDate], "\#mm\/dd\/yyyy\#"),
"Null")
DS

 

[Scott Whetsell, A.S. - WVSP]

Remove Null and leave just the two quotes.

If that doesn't work, let me know what error you are getting.

 

[DS]

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

 

[DS]

This doesn't work either.

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

DS

 

[DS]

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

 

[Scott Whetsell, A.S. - WVSP]

Try:

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

 

[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]

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

 

[DS]

OK, I tried it, Works fine with a date but without a date I get an Insert
message 3134.
Thanks
DS

 

[Scott Whetsell, A.S. - WVSP]

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.

 

[Douglas J. Steele]

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)

This doesn't work either.

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

DS

 

[Scott Whetsell, A.S. - WVSP]

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.

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)

This doesn't work either.

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

DS

 

[DS]

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

 

[DS]

Thanks Scott I appreciate your help. I used Dougs suggestion and I am up
and running.
Once again.
Thank You.
DS