data types problems

danny-t
12-15-2003, 10:03 PM
I'm having a few problems with data types, for example I have a date/time field in my access db which is changed using an update query, however if the user leaves this field blank on my form then it causes an update error
E.g. SET myDate = " & txtDate & "
if txtDate is empty there is a datatype error.

Is there a way I can avoid this error? I'm sure what i'm doing isnt very good practice so if anyone can tell me a better way i'd be very grateful.

Cheers

reboot
12-15-2003, 10:11 PM
myDate = "" & txtDate

danny-t
12-16-2003, 06:00 AM
myDate = "" & txtDate

Sorry i've still not quite got this right, how do I allow for numeric fields with no values E.g.

StrSQL = "SELECT myField, myfield2 .. Etc" _
& "FROM myTable " _
& "WHERE myField = " & myTextbox(1) & ";"

If there is no value for myTextbox and myField is a numeric field there is a missing operator error.

Cheers

Dennis DVR
12-16-2003, 06:10 AM
myDate = "" & txtDate

Sorry i've still not quite got this right, how do I allow for numeric fields with no values E.g.

StrSQL = "SELECT myField, myfield2 .. Etc" _
& "FROM myTable " _
& "WHERE myField = " & myTextbox(1) & ";"

If there is no value for myTextbox and myField is a numeric field there is a missing operator error.

Cheers

try this val("" + myTextbox(1))

danny-t
12-16-2003, 06:21 AM
myDate = "" & txtDate

Sorry i've still not quite got this right, how do I allow for numeric fields with no values E.g.

StrSQL = "SELECT myField, myfield2 .. Etc" _
& "FROM myTable " _
& "WHERE myField = " & myTextbox(1) & ";"

If there is no value for myTextbox and myField is a numeric field there is a missing operator error.

Cheers

try this val("" + myTextbox(1))

Thanks thats a bit better, but I still have problems with my foreign key fields, I've set the field to required = no but I think with the above fix it adds the value 0 into the foreign key field, this then tries to look up the value 0 in the parent table. Is there a way to have a null value in the textbox and effectively enter a null value into a numeric field from the sql statement? I suppose I could use if statements to determine if the txtbox has a value then choose to include or exclude it from the statement but there are a lot of fields this could be applied to so that would be a heck of a lot of code for what should be a simple task.

Thanks for any help guys, very much appreciated :)

Dennis DVR
12-16-2003, 06:50 AM
myDate = "" & txtDate

Sorry i've still not quite got this right, how do I allow for numeric fields with no values E.g.

StrSQL = "SELECT myField, myfield2 .. Etc" _
& "FROM myTable " _
& "WHERE myField = " & myTextbox(1) & ";"

If there is no value for myTextbox and myField is a numeric field there is a missing operator error.

Cheers

try this val("" + myTextbox(1))

Thanks thats a bit better, but I still have problems with my foreign key fields, I've set the field to required = no but I think with the above fix it adds the value 0 into the foreign key field, this then tries to look up the value 0 in the parent table. Is there a way to have a null value in the textbox and effectively enter a null value into a numeric field from the sql statement? I suppose I could use if statements to determine if the txtbox has a value then choose to include or exclude it from the statement but there are a lot of fields this could be applied to so that would be a heck of a lot of code for what should be a simple task.

Thanks for any help guys, very much appreciated :)


try to use a function that will return a null if the textbox is empty or 0

i.e.

Function MakeNull(strText as String)
if isempty(strstext) val("" + strtext) = 0 then
makenull = null
else
makenull = strText
endif
end function

StrSQL = "SELECT myField, myfield2 .. Etc" _
& "FROM myTable " _
& "WHERE myField = " & makenull(myTextbox(1)) & ";"

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum