Update table with 0 value

[vamoose]

Hello and good morning. I would like some assitance with the following update query please. I am using Access 2003 and trying to update a table using the following VBA code. The problem is that if the variable "Hours" is null it is not updating the table with a 0 as I would like it to. The field is being updated but with a null value and not 0. Any suggestions please ?

VBA code: "Update SAP_Import set Hours = '" & Nz(Hours, "0") & "'"

Thank you.

 

[PWise]

are you sure that the var hours is null not an empty string

 

[vamoose]

The variable is displayed in VBA as "Hours" = Empty

 

[JoeAtWork]

Is there any reason why you don't just make Hours a numeric variable? Then it would never be Null or Empty.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[TheAceMan1]

How are ya vamoose . . .

. . . and this:

[blue]"UpDATE SAP_Import " & _
"SET [Hours] = '0' " & _
"WHERE (Trim([Hours] & '') = '')[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

The field is being updated but with a null value and not 0.

I don't think that's possible with the VBA code you have given, unless Hours is actually a string variable set to the litteral value "Null".

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[TheAceMan1]

Howdy JoeAtWork . . .

I'm just going on a hunch!

If the data type is truly text (as the post origination shows) and the nulls are actually [blue]null strings[/blue], then [blue]Nz wont detect them[/blue] and will simply pass them back! . . . Null Strings are not Nulls and a numeric data type can't accept Null Strings (Data Type Mismatch Error).

Also, if [blue]Hours[/blue] is truly a string variable then no value defaults to a [blue]Null String![/blue]

I'm curious to see what happens . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

JoeAtWork . . .

BTW: I already tested the code I presented and it works fine! Its the typical . . .

[blue]   If [purple][b]Trim(Me!TextboxName & "") = ""[/b][/purple] Then[/blue]

. . . I'm sure you've seen before.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

AceMan - my comment was meant for the OP's original post. He said the field is updated to be Null, and if that is true than the SQL statement would have to equate to:

Update SAP_Import set Hours = Null

Actually, now I see that he is putting quotes around it, which makes it even less likely that the VBA code is making it Null (unless by Null he means a string with the word "Null", or maybe he is improperly using the term).

In any case, I think the best option is to make both the field and the variable numeric.


Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[kjv1611]

vamoose,

Did you ever get this situation solved? You never posted back. Please let us know your progress.

--

"If to err is human, then I must be some kind of human!" -Me