Help with DLookup

G

Guest

I am trying to determine whether or not a record already exist in a table
using DLookup. The table is called Month_End and contains 2 fields called
Month_End_Date and Month_End_Total. Here is the code I am trying to use:

Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date], "mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue)

It keeps throwing up an error saying:

Syntax error in string in query expression '[Month_End_Date] ='o CDM
02/2006'.

I am not sure what I am doing wrong. Any help is greatly appreciated.

C_Ascheman
 
D

Duane Hookom

Are you suggesting your Month_End_Date isn't just a date and that it
actually contains the customer and "CDM" as well as the date? If so, you
seem to be missing the ending quote:
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")

It's hard to imagine that you would need to lookup a value that is already
contained in strValue. This is kinda like looking up red cars to find out
what color they are.
 
G

Guest

Sorry once more I seem to have failed to clarify more. I am attempting to see
if the values contained in Month_End_Date of the Month_End table equals the
value strValue. What I am attempting to do is if the value is there then to
update the Month_End_Total of the Month_End table. If its not there then to
add it to the table. Maybe I am going in the completely wrong direction with
it. I figured out what I was missing earlier, and as long as there is a value
it works ok. The problem I am having now is if there is no value it gives me
this error:

Invalid use of NULL

Here is my code as it is now:

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date], "mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")
If strX = strValue Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total)
VALUES ('" & strValue & Format([Date], "mm/yyyy") & "','" & Text15.Value &
"')"
CurrentDb.Execute (sql)
End If
End Sub

If I can get it to the point where it doesn't give me errors, and either
gives me the msgbox message or adds the value into the table then I will try
and fix the code to that the instead of a msgbox updates the fields of the
table should the criteria alreay exist. Thanks for the help, and any more
will be greatly appreciated.

C_Ascheman

Duane Hookom said:
Are you suggesting your Month_End_Date isn't just a date and that it
actually contains the customer and "CDM" as well as the date? If so, you
seem to be missing the ending quote:
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")

It's hard to imagine that you would need to lookup a value that is already
contained in strValue. This is kinda like looking up red cars to find out
what color they are.
--
Duane Hookom
MS Access MVP


C_Ascheman said:
I am trying to determine whether or not a record already exist in a table
using DLookup. The table is called Month_End and contains 2 fields called
Month_End_Date and Month_End_Total. Here is the code I am trying to use:

Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date], "mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue)

It keeps throwing up an error saying:

Syntax error in string in query expression '[Month_End_Date] ='o CDM
02/2006'.

I am not sure what I am doing wrong. Any help is greatly appreciated.

C_Ascheman
Click to expand...
Click to expand...
 
D

Duane Hookom

Try this which concatenates with "&" rather than "+".

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim intCount as Integer

strValue = [Customer] & " " & "CDM" & " " & Format([Date], "mm/yyyy")
intCount = DCount("*","Month_End","[Month_End_Date] ='" & strValue &
"'")
If intCount >= 1 Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total) " & _
"VALUES ('" & strValue & "','" & Me.Text15 & "')"
CurrentDb.Execute (sql), dbFailOnError
End If
End Sub

--
Duane Hookom
MS Access MVP

C_Ascheman said:
Sorry once more I seem to have failed to clarify more. I am attempting to
see
if the values contained in Month_End_Date of the Month_End table equals
the
value strValue. What I am attempting to do is if the value is there then
to
update the Month_End_Total of the Month_End table. If its not there then
to
add it to the table. Maybe I am going in the completely wrong direction
with
it. I figured out what I was missing earlier, and as long as there is a
value
it works ok. The problem I am having now is if there is no value it gives
me
this error:

Invalid use of NULL

Here is my code as it is now:

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date], "mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")
If strX = strValue Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total)
VALUES ('" & strValue & Format([Date], "mm/yyyy") & "','" & Text15.Value &
"')"
CurrentDb.Execute (sql)
End If
End Sub

If I can get it to the point where it doesn't give me errors, and either
gives me the msgbox message or adds the value into the table then I will
try
and fix the code to that the instead of a msgbox updates the fields of the
table should the criteria alreay exist. Thanks for the help, and any more
will be greatly appreciated.

C_Ascheman

Duane Hookom said:
Are you suggesting your Month_End_Date isn't just a date and that it
actually contains the customer and "CDM" as well as the date? If so, you
seem to be missing the ending quote:
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")

It's hard to imagine that you would need to lookup a value that is
already
contained in strValue. This is kinda like looking up red cars to find out
what color they are.
--
Duane Hookom
MS Access MVP


C_Ascheman said:
I am trying to determine whether or not a record already exist in a
table
using DLookup. The table is called Month_End and contains 2 fields
called
Month_End_Date and Month_End_Total. Here is the code I am trying to
use:

Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date],
"mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date]
='" &
strValue)

It keeps throwing up an error saying:

Syntax error in string in query expression '[Month_End_Date] ='o CDM
02/2006'.

I am not sure what I am doing wrong. Any help is greatly appreciated.

C_Ascheman
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Duane. I got it working. Here is the code as it sits now:

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim intCount As Integer

strValue = [Customer] + " " + "CDM" + " "
intCount = DCount("*", "Month_End", "[Month_End_Date]='" & strValue & "'")
If intCount = 1 Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total)
VALUES ('" & strValue & Format([Date], "mm/yyyy") & "','" & Text15.Value &
"')"
CurrentDb.Execute (sql)
End If
End Sub

Now onto figuring out how to append the Month_End_Total if the record
already exists. Thanks again.

C_Ascheman

Duane Hookom said:
Try this which concatenates with "&" rather than "+".

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim intCount as Integer

strValue = [Customer] & " " & "CDM" & " " & Format([Date], "mm/yyyy")
intCount = DCount("*","Month_End","[Month_End_Date] ='" & strValue &
"'")
If intCount >= 1 Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total) " & _
"VALUES ('" & strValue & "','" & Me.Text15 & "')"
CurrentDb.Execute (sql), dbFailOnError
End If
End Sub

--
Duane Hookom
MS Access MVP

C_Ascheman said:
Sorry once more I seem to have failed to clarify more. I am attempting to
see
if the values contained in Month_End_Date of the Month_End table equals
the
value strValue. What I am attempting to do is if the value is there then
to
update the Month_End_Total of the Month_End table. If its not there then
to
add it to the table. Maybe I am going in the completely wrong direction
with
it. I figured out what I was missing earlier, and as long as there is a
value
it works ok. The problem I am having now is if there is no value it gives
me
this error:

Invalid use of NULL

Here is my code as it is now:

Private Sub MonthEndUpdate()
Dim sql As String
Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date], "mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")
If strX = strValue Then
MsgBox "Already exists."
Else
sql = "INSERT INTO Month_End (Month_End_Date, Month_End_Total)
VALUES ('" & strValue & Format([Date], "mm/yyyy") & "','" & Text15.Value &
"')"
CurrentDb.Execute (sql)
End If
End Sub

If I can get it to the point where it doesn't give me errors, and either
gives me the msgbox message or adds the value into the table then I will
try
and fix the code to that the instead of a msgbox updates the fields of the
table should the criteria alreay exist. Thanks for the help, and any more
will be greatly appreciated.

C_Ascheman

Duane Hookom said:
Are you suggesting your Month_End_Date isn't just a date and that it
actually contains the customer and "CDM" as well as the date? If so, you
seem to be missing the ending quote:
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date] ='" &
strValue & "'")

It's hard to imagine that you would need to lookup a value that is
already
contained in strValue. This is kinda like looking up red cars to find out
what color they are.
--
Duane Hookom
MS Access MVP


I am trying to determine whether or not a record already exist in a
table
using DLookup. The table is called Month_End and contains 2 fields
called
Month_End_Date and Month_End_Total. Here is the code I am trying to
use:

Dim strValue As String
Dim strX As String

strValue = [Customer] + " " + "CDM" + " " & Format([Date],
"mm/yyyy")
strX = DLookup("[Month_End_Date]", "Month_End", "[Month_End_Date]
='" &
strValue)

It keeps throwing up an error saying:

Syntax error in string in query expression '[Month_End_Date] ='o CDM
02/2006'.

I am not sure what I am doing wrong. Any help is greatly appreciated.

C_Ascheman
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with code issue. 11
Date programming question. 6
DLookUp help 4
syntax error at run time 3
DlookUp Help 6
DLookup with 3 criteria - one of them boolean 2
Help With Module - sorry, one more question 7
Dlookup and Dates 2

Top