Need help with code issue.

[Guest]

Here is the code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total = Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date], "mm/yyyy")
CurrentDb.Execute (sql)

Here is the error I get:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date = o CDM
03/2006'.

At a complete loss as to what I am missing. I have been looking at this, and
trying to figure it out since last friday. Can someone please help me.

C_Ascheman

 

[Allen Browne]

Between those 2 lines of code, add:
Debug.Print sql

When it fails, open the Immediate Window (Ctrl+G) and see what prints there.

If Month_End_Date is a Date/Time field, you need to see a result such as:
WHERE Month_End_Date = #6/30/2006#;

If it is a Text type field, you need to add quotes inside the string around
the value. If you are not sure how to do that, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Keith Wilby]

Here is the code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date],
"mm/yyyy")
CurrentDb.Execute (sql)

Here is the error I get:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date = o
CDM
03/2006'.

At a complete loss as to what I am missing. I have been looking at this,
and
trying to figure it out since last friday. Can someone please help me.

C_Ascheman

Try

sql = "UPDATE Month_End SET Month_End_Total = " & Text15 & " WHERE
Month_End_Date =" & strValue & Format([Date], "mm/yyyy")

Regards,
Keith.
www.keithwilby.com

 

[Guest]

I tried what both of you suggested. Everything is in text format. Doing what
you suggested Allen I get his in the immediate window:

UPDATE Month_End
SET Month_End_Total = Text15.value
WHERE [Month_End_Date] =o CDM 03/2006

I still at a loss as to whats wrong with it. Month_End is a table in my
access db. Month_End_Total and Month_End_Date are fields within tha table.
Text15.value is a text box on my form, and o CDM 03/2006 is a unique value
that is generated by combining strValue and format([Date],"mm/yyyy"). I need
serious help with this guys. Its already a week behind, and the boss is
getting a little upset now. I am new to this company, and have an associates
in minor vb programming (very minor). This is not something I was taught at
the college I went to so please help me with this. I am learning from it.

Keith I tried what you wrote, and it gives the exact same error. Thanks for
you help so far guys, but I am really desperate. Please help.

C_Ascheman

Here is the code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date],
"mm/yyyy")
CurrentDb.Execute (sql)

Here is the error I get:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date = o
CDM
03/2006'.

At a complete loss as to what I am missing. I have been looking at this,
and
trying to figure it out since last friday. Can someone please help me.

C_Ascheman

Try

sql = "UPDATE Month_End SET Month_End_Total = " & Text15 & " WHERE
Month_End_Date =" & strValue & Format([Date], "mm/yyyy")

Regards,
Keith.
www.keithwilby.com

 

[Tim Ferguson]

I still at a loss as to whats wrong with it. Month_End is a table in
my access db. Month_End_Total and Month_End_Date are fields within tha
table. Text15.value is a text box on my form, and o CDM 03/2006 is a
unique value that is generated by combining strValue and
format([Date],"mm/yyyy"). I need

You could read a little in the help files about passing values to the SQL
engine and delimiters. If you want something to end up looking like this:

WHERE Month_End_Date = "o CDM 03/2006"

(and you do...), then you need to insert the quote chars too:

jetSQL = jetSQL & vbNewLine & _
"""" & "o CDM 03/2006" & """"


Hope that helps
Of course, correcting the table design would help too.

Tim F

 

[Guest]

I understand that there are issues. Unfortunately due to time constraints I
can not take care of those at the moment. I will though once the program is
operational. My boss has no concept of what it takes to create any program,
or the amount of time. He gave me 2 weeks to get it done. Its a week late
now. I value my job, and have come here to get help from you guys. Which you
have graciously provided. I am not though an expert like the rest of you. I
have read through help files by the hundreds, scoured the internet, and much
more to try and get this done. Once its up an running I can then create a
secondary copy to make the corrections to, and then import all data from the
db in use to the fixed one. I need to know what is wrong with this code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total = Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date], "mm/yyyy")
Debug.Print sql
CurrentDb.Execute (sql)

and why it gives me this error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date =o CDM
03/2006'.

I really do appreciate the help and advice. I have only been programming for
4 years, so my experience is very limited. Most entailing minor vb scripting
to access and excel. This is the first major program my boss has requested,
and I am trying to do the best I can. Given more time I could set everything
up more properly. Unfortunately I don't have that luxury. I am begging anyone
that can help me please tell me exactly what is wrong with the code, and what
it needs to be to work please.

C_Ascheman

I still at a loss as to whats wrong with it. Month_End is a table in
my access db. Month_End_Total and Month_End_Date are fields within tha
table. Text15.value is a text box on my form, and o CDM 03/2006 is a
unique value that is generated by combining strValue and
format([Date],"mm/yyyy"). I need

You could read a little in the help files about passing values to the SQL
engine and delimiters. If you want something to end up looking like this:

WHERE Month_End_Date = "o CDM 03/2006"

(and you do...), then you need to insert the quote chars too:

jetSQL = jetSQL & vbNewLine & _
"""" & "o CDM 03/2006" & """"


Hope that helps
Of course, correcting the table design would help too.

Tim F

 

[Allen Browne]

You might try adding those quotes referred to in the last half of my
previous reply.

 

[Guest]

I tried Allen. Not sure if what I was doing was correct or not. I tried this:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total = Text15.value"
& vbNewLine & "WHERE Month_End_Date =" ""& strValue & Format([Date],
"mm/yyyy")""
Debug.Print sql
CurrentDb.Execute (sql)

but it gives me an expected end of statement error here at the first double
quotes in ""& strValue & Format([Date], "mm/yyyy")"". Not sure where else to
try and put them. I am just trying to update Month_End_Total of the Month_End
table with the value from Text15 where Month_End_Date of the Month_End table
is equal to strValue & Format([Date],"mm/yyyy"). I have tried everything
suggested (except redoing the entire db for previously mentioned reasons). I
just need it to change the one value in the table. If you got another
suggestion other then sql that can do the same thing then please let me know.
Thanks.

C_Ascheman

You might try adding those quotes referred to in the last half of my
previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I understand that there are issues. Unfortunately due to time constraints I
can not take care of those at the moment. I will though once the program
is
operational. My boss has no concept of what it takes to create any
program,
or the amount of time. He gave me 2 weeks to get it done. Its a week late
now. I value my job, and have come here to get help from you guys. Which
you
have graciously provided. I am not though an expert like the rest of you.
I
have read through help files by the hundreds, scoured the internet, and
much
more to try and get this done. Once its up an running I can then create a
secondary copy to make the corrections to, and then import all data from
the
db in use to the fixed one. I need to know what is wrong with this code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date],
"mm/yyyy")
Debug.Print sql
CurrentDb.Execute (sql)

and why it gives me this error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date =o CDM
03/2006'.

I really do appreciate the help and advice. I have only been programming
for
4 years, so my experience is very limited. Most entailing minor vb
scripting
to access and excel. This is the first major program my boss has
requested,
and I am trying to do the best I can. Given more time I could set
everything
up more properly. Unfortunately I don't have that luxury. I am begging
anyone
that can help me please tell me exactly what is wrong with the code, and
what
it needs to be to work please.

C_Ascheman

 

[Allen Browne]

Try:
sql = "UPDATE Month_End " & vbCrLf & _
"SET Month_End_Total = " & Nz(Me.Text15, 0) & vbCrLf & _
"WHERE Month_End_Date = """ & strValue & Format(Date, "mm/yyyy") & """;"

Debug.Print sql

That will use today's date. If you have a field named Date, you still have
problems.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried Allen. Not sure if what I was doing was correct or not. I tried
this:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" ""& strValue & Format([Date],
"mm/yyyy")""
Debug.Print sql
CurrentDb.Execute (sql)

but it gives me an expected end of statement error here at the first
double
quotes in ""& strValue & Format([Date], "mm/yyyy")"". Not sure where else
to
try and put them. I am just trying to update Month_End_Total of the
Month_End
table with the value from Text15 where Month_End_Date of the Month_End
table
is equal to strValue & Format([Date],"mm/yyyy"). I have tried everything
suggested (except redoing the entire db for previously mentioned reasons).
I
just need it to change the one value in the table. If you got another
suggestion other then sql that can do the same thing then please let me
know.
Thanks.

C_Ascheman

You might try adding those quotes referred to in the last half of my
previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I understand that there are issues. Unfortunately due to time
constraints I
can not take care of those at the moment. I will though once the
program
is
operational. My boss has no concept of what it takes to create any
program,
or the amount of time. He gave me 2 weeks to get it done. Its a week
late
now. I value my job, and have come here to get help from you guys.
Which
you
have graciously provided. I am not though an expert like the rest of
you.
I
have read through help files by the hundreds, scoured the internet, and
much
more to try and get this done. Once its up an running I can then create
a
secondary copy to make the corrections to, and then import all data
from
the
db in use to the fixed one. I need to know what is wrong with this
code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date],
"mm/yyyy")
Debug.Print sql
CurrentDb.Execute (sql)

and why it gives me this error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date =o
CDM
03/2006'.

I really do appreciate the help and advice. I have only been
programming
for
4 years, so my experience is very limited. Most entailing minor vb
scripting
to access and excel. This is the first major program my boss has
requested,
and I am trying to do the best I can. Given more time I could set
everything
up more properly. Unfortunately I don't have that luxury. I am begging
anyone
that can help me please tell me exactly what is wrong with the code,
and
what
it needs to be to work please.

 

[Guest]

Thank you so very very much Allen. You are a godsend. That worked perfectly.
Now maybe I can sleep a little better tonight. If you could do me one more
favor please I would be very greatful. Can you explain to me why what I did
wasn't working. In your code you have "SET Month_End_Total = " & Nz ... what
is the Nz for. Thats the first time I have ever seen that in any code. I now
see though where I needed to put the ", and how to place them. Once more
thank you so very much.

C_Ascheman

Try:
sql = "UPDATE Month_End " & vbCrLf & _
"SET Month_End_Total = " & Nz(Me.Text15, 0) & vbCrLf & _
"WHERE Month_End_Date = """ & strValue & Format(Date, "mm/yyyy") & """;"

Debug.Print sql

That will use today's date. If you have a field named Date, you still have
problems.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried Allen. Not sure if what I was doing was correct or not. I tried
this:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" ""& strValue & Format([Date],
"mm/yyyy")""
Debug.Print sql
CurrentDb.Execute (sql)

but it gives me an expected end of statement error here at the first
double
quotes in ""& strValue & Format([Date], "mm/yyyy")"". Not sure where else
to
try and put them. I am just trying to update Month_End_Total of the
Month_End
table with the value from Text15 where Month_End_Date of the Month_End
table
is equal to strValue & Format([Date],"mm/yyyy"). I have tried everything
suggested (except redoing the entire db for previously mentioned reasons).
I
just need it to change the one value in the table. If you got another
suggestion other then sql that can do the same thing then please let me
know.
Thanks.

C_Ascheman

You might try adding those quotes referred to in the last half of my
previous reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I understand that there are issues. Unfortunately due to time
constraints I
can not take care of those at the moment. I will though once the
program
is
operational. My boss has no concept of what it takes to create any
program,
or the amount of time. He gave me 2 weeks to get it done. Its a week
late
now. I value my job, and have come here to get help from you guys.
Which
you
have graciously provided. I am not though an expert like the rest of
you.
I
have read through help files by the hundreds, scoured the internet, and
much
more to try and get this done. Once its up an running I can then create
a
secondary copy to make the corrections to, and then import all data
from
the
db in use to the fixed one. I need to know what is wrong with this
code:

sql = "UPDATE Month_End" & vbNewLine & "SET Month_End_Total =
Text15.value"
& vbNewLine & "WHERE Month_End_Date =" & strValue & Format([Date],
"mm/yyyy")
Debug.Print sql
CurrentDb.Execute (sql)

and why it gives me this error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Month_End_Date =o
CDM
03/2006'.

I really do appreciate the help and advice. I have only been
programming
for
4 years, so my experience is very limited. Most entailing minor vb
scripting
to access and excel. This is the first major program my boss has
requested,
and I am trying to do the best I can. Given more time I could set
everything
up more properly. Unfortunately I don't have that luxury. I am begging
anyone
that can help me please tell me exactly what is wrong with the code,
and
what
it needs to be to work please.

 

[Allen Browne]

It was probably the quotes that fixed the problem.

The Nz() supplies a value to use for null.
Otherwise the SQL statement would not be correct if the text box had no
value.
For example, instead of reading:
SET Month_End_Total = 0 WHERE ...
it would not work if the text box was blank and so the SQL statement became:
SET Month_End_Total = WHERE ...

That issue is #2 in this article:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[Tim Ferguson]

. Can you explain to me why what I did
wasn't working.

What was suggested:

"""" & "o CDM 03/2006" & """"

What you coded:

"WHERE Month_End_Date =" ""& _
strValue & Format([Date], "mm/yyyy")""

.... which can't be right becuase it wouldn't get past the editor. The
trick is that in order to put quote(") marks inside strings, you have to
double them: that is why

""""

is a string containing one quote char; and

"WHERE Month_End_Date = """ & stuff & """"

has one quote char at each end of the stuff, which is what you want.


Hope that makes it a bit clearer.
All the best


Tim F