 | |
SQL Help - Update Statement
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: SQL Help - Update Statement -
02-26-2008
, 12:14 PM
|
On Feb 26, 4:57 pm, Salad <o... (AT) vinegar (DOT) com> wrote: prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 3:58 pm, prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 2:52 pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote: The string you need will depend on the data types of your various fields. For a Date/Time field, you need to delimit the value with #. For Text fields, use quotes. For number fields, you don't need any delimiter. Additionally, the Nz() is not adequate here. You need the word Null in the string if the field is null (and so you are trying to insert the Null value.) The literal date in the SQL string needs to be in mdy format (not your own local format.) It will be easier of you build a string, because you can then examine the contents to see if it looks right. The following example demonstrates the different data type, on the assumption that: INV_DATE is a Date/Time field, and RcdFm_PdTo is a Number field, and Chq_No is a Text field. Adjust and complete the string to suit. The example prints the string to the Immediate Window. If it fails, press Ctrl+G and look at the string. Compare it with a query that does work, and see where you went wrong. Here's the incomplete example that illustrates the different data types: Dim strSql As String Const strcJetDate "\#mm\/dd\/yyyy\#" strSql = "UPDATE JVTable SET INV_DATE = " & _ IIf(IsNull(Me.txt_Date), "Null", Format(Me.txt_Date, strcJetDate)) & _ ", RcdFm_PdTo = " & _ IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", Me.txt_RcdFm_PdTo) & _ ", Chq_No = " & _ IIf(IsNull(Me.txt_Chq_No), "Null", """" & Me.txt_Chq_No & """") & _ ... Debug.Print strSql CurrentDb.Execute strSql, dbFailOnError -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users -http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. prakashwadhw... (AT) gmail (DOT) com> wrote in message news:6809ebe0-5500-490f-9eac-a3afe57c1766 (AT) v3g2000hsc (DOT) googlegroups.com... I'm having a little problem with this Update statement and have been toiling away to no good. Could anyone please correct my syntax. Here's the code: CurrentDb.Execute "UPDATE JVTable SET JVTable.INV_DATE = " & DMY(Me.Txt_Date) & ", " _ "JVTable.RcdFm_PdTo = '" & nz(me.txt_RcdFm_PdTo) & "'" _ "JVTable.Chq_No = '" & nz(me.txt_Chq_No) & "'" _ "JVTable.Chq_Date = '" & DMY(Me.Txt_Date) & ", " _ "JVTable.Bank = '" & nz(me.txt_Bank) & "'" _ "JVTable.Settled_Bill_No = '" & nz(me.txt_Settled_Bill_No) & "'" Best Rgds, Prakash Thx Allen !! You've really covered almost all sides here. Firstly, besides the 2 dates ... they are all text fields (I should've specified that at the outset ...Sorry !!) Secondly, my DMY function takes care of the dates by delimiting with #, etc. Two questions: (pls excuse my ignorance) 1) You've used the word "Null" in the iif statement. Does that actually input the string "Null" into the field or just the NULL value ? 2) What does the dbFailOnError do ? I toyed around with your example & tailored it to suit my needs. It seems to work. Here's my code ... StrSql = "UPDATE JVTable SET JVTable.INV_DATE = " & _ IIf(IsNull(Me.Txt_Date), "Null", DMY(Me.Txt_Date)) & ", " & _ "JVTable.RcdFm_PdTo = " & _ IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_PdTo & """") & ", " & _ "JVTable.Chq_No = " & _ IIf(IsNull(Me.txt_Chq_No), "Null", """" & Me.txt_Chq_No & """") & ", " & _ "JVTable.Chq_Date = " & _ IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date)) & ", " & _ "JVTable.Bank = " & _ IIf(IsNull(Me.txt_Bank), "Null", """" & Me.txt_Bank & """") & ", " & _ "JVTable.Settled_Bill_No = " & _ IIf(IsNull(Me.txt_Settled_Bill_Nos), "Null", """" & Me.txt_Settled_Bill_Nos & """") If I have any further difficulty, I'll post back. Thank you very, very, much. Best Rgds, Prakash. If I leave txt_Chq_Date empty on the form i get the message "Invalid Use of Null" After I fill all the fields and try to run the sql i get: "Too few Parameters. Expected 1." Can anyone please point out where I'm going wrong ? Best Rgds, Prakash. If you have something like Debug.Print strSQL and it fails, one thing you can do is to create a new query (Query/New/Design/Close. You now have a blank Query builder. Now go into the Debug/Immediate window. You'll see the offending strSQL line. Copy it to the clipboard. Go back the the Query builder and select View/SQL and paste the code into the query window. Now run it either from that window or the Design window. This might help track down the error. Sorry Salad ... didn't read properly ... thought that Allen had answered this thread. Thank you very much. Your suggestion was spot-on & worked like a charm. One tiny thing bothering me though ... If I leave txt_Chq_Date empty on the form, i get the message "Invalid Use of Null" |
is to provide us with an error number as well as the error description.
If you have an error routine, put in something like
msgbox err.number & space(2) & err.description
FYI, Invalid Use Of Null is err number 94.
Anyway...I really don't know. The error line is
IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date))
The problem is...where's the error comming from? Is from the table or
is it from the function DMY()? When it breaks, I would hold the mouse
over the above line. What is the value of txt_chq_Date? It will tell
you as you slowly drag it across the line. You could go into the
Immediate/Debug window and enter
? DMY(Me.txt_Chq_Date)
It's possible the function couldn't process the value.
You could also, from within the debug window, enter
? IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date))
If the returned value from above is OK, then I'd check your table
settings. Maybe the Required setting is True for that field and you are
attempting to insert a Null. Also check the AllowZeroLength settings.
As I see it, the field will contain the word "Null" or else it will
contact a date field converted to text.
One time I was sitting around attempting to get all the code formats etc
written correcty (# around dates, "" around text fields, etc) and wasted
a bunch of my time. So I bypassed my frustration and entered
rst.AddNew
rst!Field = value
rst.Update
and it worked.
Anyway, I think if you get into the debug window and test the two lines
above out you'll be closer to finding the problem.
Hip Hip Hurray
http://www.youtube.com/watch?v=bXk5PWs0EEs
Quote:
|
How do I correct this ? It's very frustrating. Thx again. Best Rgds, Prakash. |
#12
| |||
| |||
|
|
prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 4:57 pm, Salad <o... (AT) vinegar (DOT) com> wrote: prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 3:58 pm, prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 2:52 pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote: The string you need will depend on the data types of your various fields. For a Date/Time field, you need to delimit the value with #. For Text fields, use quotes. For number fields, you don't need any delimiter. Additionally, the Nz() is not adequate here. You need the word Null in the string if the field is null (and so you are trying to insert the Null value.) The literal date in the SQL string needs to be in mdy format (not your own local format.) It will be easier of you build a string, because you can then examine the contents to see if it looks right. The following example demonstrates the different data type, on the assumption that: INV_DATE is a Date/Time field, and RcdFm_PdTo is a Number field, and Chq_No is a Text field. Adjust and complete the string to suit. The example prints the string to the Immediate Window. If it fails, press Ctrl+G and look at the string. Compare it with a query that does work, and see where you went wrong. Here's the incomplete example that illustrates the different data types: Dim strSql As String Const strcJetDate "\#mm\/dd\/yyyy\#" strSql = "UPDATE JVTable SET INV_DATE = " & _ IIf(IsNull(Me.txt_Date), "Null", Format(Me.txt_Date, strcJetDate)) & _ ", RcdFm_PdTo = " & _ IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", Me.txt_RcdFm_PdTo) & _ ", Chq_No = " & _ IIf(IsNull(Me.txt_Chq_No), "Null", """" & Me.txt_Chq_No & """") & _ ... Debug.Print strSql CurrentDb.Execute strSql, dbFailOnError -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users -http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. prakashwadhw... (AT) gmail (DOT) com> wrote in message news:6809ebe0-5500-490f-9eac-a3afe57c1766 (AT) v3g2000hsc (DOT) googlegroups.com... I'm having a little problem with this Update statement and have been toiling away to no good. Could anyone please correct my syntax. Here's the code: CurrentDb.Execute "UPDATE JVTable SET JVTable.INV_DATE = " & DMY(Me.Txt_Date) & ", " _ "JVTable.RcdFm_PdTo = '" & nz(me.txt_RcdFm_PdTo) & "'" _ "JVTable.Chq_No = '" & nz(me.txt_Chq_No) & "'" _ "JVTable.Chq_Date = '" & DMY(Me.Txt_Date) & ", " _ "JVTable.Bank = '" & nz(me.txt_Bank) & "'" _ "JVTable.Settled_Bill_No = '" & nz(me.txt_Settled_Bill_No) & "'" Best Rgds, Prakash Thx Allen !! You've really covered almost all sides here. Firstly, besides the 2 dates ... they are all text fields (I should've specified that at the outset ...Sorry !!) Secondly, my DMY function takes care of the dates by delimiting with #, etc. Two questions: (pls excuse my ignorance) 1) You've used the word "Null" in the iif statement. Does that actually input the string "Null" into the field or just the NULL value ? 2) What does the dbFailOnError do ? I toyed around with your example & tailored it to suit my needs. It seems to work. Here's my code ... StrSql = "UPDATE JVTable SET JVTable.INV_DATE = " & _ IIf(IsNull(Me.Txt_Date), "Null", DMY(Me.Txt_Date)) & ", " & _ "JVTable.RcdFm_PdTo = " & _ IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_PdTo & """") & ", " & _ "JVTable.Chq_No = " & _ IIf(IsNull(Me.txt_Chq_No), "Null", """" & Me.txt_Chq_No & """") & ", " & _ "JVTable.Chq_Date = " & _ IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date)) & ", " & _ "JVTable.Bank = " & _ IIf(IsNull(Me.txt_Bank), "Null", """" & Me.txt_Bank & """") & ", " & _ "JVTable.Settled_Bill_No = " & _ IIf(IsNull(Me.txt_Settled_Bill_Nos), "Null", """" & Me.txt_Settled_Bill_Nos & """") If I have any further difficulty, I'll post back. Thank you very, very, much. Best Rgds, Prakash. If I leave txt_Chq_Date empty on the form i get the message "Invalid Use of Null" After I fill all the fields and try to run the sql i get: "Too few Parameters. Expected 1." Can anyone please point out where I'm going wrong ? Best Rgds, Prakash. If you have something like Debug.Print strSQL and it fails, one thing you can do is to create a new query (Query/New/Design/Close. You now have a blank Query builder. Now go into the Debug/Immediate window. You'll see the offending strSQL line. Copy it to the clipboard. Go back the the Query builder and select View/SQL and paste the code into the query window. Now run it either from that window or the Design window. This might help track down the error. Sorry Salad ... didn't read properly ... thought that Allen had answered this thread. Thank you very much. Your suggestion was spot-on & worked like a charm. One tiny thing bothering me though ... If I leave txt_Chq_Date empty on the form, i get the message "Invalid Use of Null" One thing that will help us, in case you have a problem in the future, is to provide us with an error number as well as the error description. If you have an error routine, put in something like msgbox err.number & space(2) & err.description FYI, Invalid Use Of Null is err number 94. Anyway...I really don't know. The error line is IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date)) The problem is...where's the error comming from? Is from the table or is it from the function DMY()? When it breaks, I would hold the mouse over the above line. What is the value of txt_chq_Date? It will tell you as you slowly drag it across the line. You could go into the Immediate/Debug window and enter ? DMY(Me.txt_Chq_Date) It's possible the function couldn't process the value. You could also, from within the debug window, enter ? IIf(IsNull(Me.txt_chq_Date), "Null", DMY(Me.txt_Chq_Date)) If the returned value from above is OK, then I'd check your table settings. Maybe the Required setting is True for that field and you are attempting to insert a Null. Also check the AllowZeroLength settings. As I see it, the field will contain the word "Null" or else it will contact a date field converted to text. One time I was sitting around attempting to get all the code formats etc written correcty (# around dates, "" around text fields, etc) and wasted a bunch of my time. So I bypassed my frustration and entered rst.AddNew rst!Field = value rst.Update and it worked. Anyway, I think if you get into the debug window and test the two lines above out you'll be closer to finding the problem. Hip Hip Hurrayhttp://www.youtube.com/watch?v=bXk5PWs0EEs How do I correct this ? It's very frustrating. Thx again. Best Rgds, Prakash. |
Hi Salad ... Wow !!! You really are amazing! The way you're walking
me through this ... so patiently. Thx a ton !!
I followed a bit of what you've advised & have sort of some to the
conclusion the problem lies in my DMY function. Basically it's
expecting a date (in dd/mm/yy format) & if it gets a blank filed i.e.
a NULL value, it just isn't able to handle it. I had got this DMY
function from this NG itself and would like to modify it so that in
case the parameter passed is a NULL, then it should probably just
return the NULL value back itself. Am I thinking correctly ? Here's my
DMY function in a general module.
Public Function DMY(DateExpr As Date) As String
' Converts any date format to mm/dd/yy type date format
' for accurate date calculations and comparisons
DMY = "#" & Month(DateExpr) & "/" & Day(DateExpr) & "/" &
Year(DateExpr) & "#"
End Function
How should I go about modifying this function ?
Thx a million for all you efforts.
Best Rgds,
Prakash.
#13
| |||
| |||
|
|
On Feb 26, 10:14 pm, Salad <o... (AT) vinegar (DOT) com> wrote: I followed a bit of what you've advised & have sort of some to the conclusion the problem lies in my DMY function. Basically it's expecting a date (in dd/mm/yy format) & if it gets a blank filed i.e. a NULL value, it just isn't able to handle it. I had got this DMY function from this NG itself and would like to modify it so that in case the parameter passed is a NULL, then it should probably just return the NULL value back itself. Am I thinking correctly ? Here's my DMY function in a general module. Public Function DMY(DateExpr As Date) As String ' Converts any date format to mm/dd/yy type date format ' for accurate date calculations and comparisons DMY = "#" & Month(DateExpr) & "/" & Day(DateExpr) & "/" & Year(DateExpr) & "#" End Function How should I go about modifying this function ? Thx a million for all you efforts. Best Rgds, Prakash. It appears the DMY accepts a date and passes it back as a string. I |
dates, nulls. So I'd allow DMY to accept a null argument and pass back
a variant.
I'm not sure why you need to use the Month/Day/Year functions...so I
excluded them.
Public Function DMY(DateExpr As Variant) As Variant
If Not IsNull(DateExpr) Then
'verify DateExpr is really a date
If IsDate(DateExpr) Then DMY = "#" & DateExpr & "#"
Endif
End Function
SanFrancisco
http://www.youtube.com/watch?v=vn4ovawULGo
#14
| |||
| |||
|
|
prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 10:14 pm, Salad <o... (AT) vinegar (DOT) com> wrote: I followed a bit of what you've advised & have sort of some to the conclusion the problem lies in my DMY function. Basically it's expecting a date (in dd/mm/yy format) & if it gets a blank filed i.e. a NULL value, it just isn't able to handle it. I had got this DMY function from this NG itself and would like to modify it so that in case the parameter passed is a NULL, then it should probably just return the NULL value back itself. Am I thinking correctly ? Here's my DMY function in a general module. Public Function DMY(DateExpr As Date) As String ' Converts any date format to mm/dd/yy type date format ' for accurate date calculations and comparisons DMY = "#" & Month(DateExpr) & "/" & Day(DateExpr) & "/" & Year(DateExpr) & "#" End Function How should I go about modifying this function ? Thx a million for all you efforts. Best Rgds, Prakash. It appears the DMY accepts a date and passes it back as a string. I might do a couple of things. A variant can accept numbers, strings, dates, nulls. So I'd allow DMY to accept a null argument and pass back a variant. I'm not sure why you need to use the Month/Day/Year functions...so I excluded them. Public Function DMY(DateExpr As Variant) As Variant If Not IsNull(DateExpr) Then 'verify DateExpr is really a date If IsDate(DateExpr) Then DMY = "#" & DateExpr & "#" Endif End Function SanFranciscohttp://www.youtube.com/watch?v=vn4ovawULGo |
I used your If Not IsNull(DateExpr) Then statement, but still got
the same error.
It's only when I changed the DMY func to expect & return Variants
(like you explained), that the error disappeared. It now seems to work
fine.
Obviously as you mentioned, the Date/String type cannot hold NULLS.
Also ... I used the Month/Day/Year functions because here in Muscat
(Oman) and India, people enter dates in the dd/mm/yy format. The
function then converts it to mm/dd/yy format for proper comparison.
It's been working pefectly so far (unless you have any further
suggestions to fine-tune it).
I really cannot thank you enough for all the help & support extended
patiently. I'm very very grateful.
Thx & Best Rgds,
Prakash.
#15
| |||
| |||
|
|
On Feb 27, 9:52 am, Salad <o... (AT) vinegar (DOT) com> wrote: prakashwadhw... (AT) gmail (DOT) com wrote: On Feb 26, 10:14 pm, Salad <o... (AT) vinegar (DOT) com> wrote: I followed a bit of what you've advised & have sort of some to the conclusion the problem lies in my DMY function. Basically it's expecting a date (in dd/mm/yy format) & if it gets a blank filed i.e. a NULL value, it just isn't able to handle it. I had got this DMY function from this NG itself and would like to modify it so that in case the parameter passed is a NULL, then it should probably just return the NULL value back itself. Am I thinking correctly ? Here's my DMY function in a general module. Public Function DMY(DateExpr As Date) As String ' Converts any date format to mm/dd/yy type date format ' for accurate date calculations and comparisons DMY = "#" & Month(DateExpr) & "/" & Day(DateExpr) & "/" & Year(DateExpr) & "#" End Function How should I go about modifying this function ? Thx a million for all you efforts. Best Rgds, Prakash. It appears the DMY accepts a date and passes it back as a string. I might do a couple of things. A variant can accept numbers, strings, dates, nulls. So I'd allow DMY to accept a null argument and pass back a variant. I'm not sure why you need to use the Month/Day/Year functions...so I excluded them. Public Function DMY(DateExpr As Variant) As Variant If Not IsNull(DateExpr) Then 'verify DateExpr is really a date If IsDate(DateExpr) Then DMY = "#" & DateExpr & "#" Endif End Function SanFranciscohttp://www.youtube.com/watch?v=vn4ovawULGo I used your If Not IsNull(DateExpr) Then statement, but still got the same error. It's only when I changed the DMY func to expect & return Variants (like you explained), that the error disappeared. It now seems to work fine. Obviously as you mentioned, the Date/String type cannot hold NULLS. Also ... I used the Month/Day/Year functions because here in Muscat (Oman) and India, people enter dates in the dd/mm/yy format. The function then converts it to mm/dd/yy format for proper comparison. It's been working pefectly so far (unless you have any further suggestions to fine-tune it). |
wouldn't have been able to find it on a map otherwise.
Regarding your last statement. Yes...and no. I now understand why you
needed to do what you are doing. Go into the debug window and see if
this works
d = date()
? d
2/26/2008
? "#" & format(d,"mm/dd/yyyy") & "#"
#02/26/2008#
Quote:
|
I really cannot thank you enough for all the help & support extended patiently. I'm very very grateful. |
of knowledge.
Camel Girls
http://www.youtube.com/watch?v=gg7WG6tCbrw
Indian Montage
http://www.youtube.com/watch?v=d4BoY4MekCU&feature=user
Quote:
|
Thx & Best Rgds, Prakash. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.