Type Mismatch in dLookup

D

Doctor

On strContact I keep getting a type mismatch error and I'm not sure why.
Below I pasted the code performing the DLookup and also the SQL for the query
the DLookup is searching. Me.ContactChurch is a combo box in the form and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone from
the same church exists with the same last name and if so it asks the user if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState, strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value And " [ContactLastName]=" & Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID = Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
 
J

Jeff Boyce

I'll guess that your [ContactLastName] field is a text-type field. Maybe
you need to "quote" it, along with any other text-type fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Doctor said:
On strContact I keep getting a type mismatch error and I'm not sure why.
Below I pasted the code performing the DLookup and also the SQL for the query
the DLookup is searching. Me.ContactChurch is a combo box in the form and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone from
the same church exists with the same last name and if so it asks the user if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState, strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value And " [ContactLastName]=" & Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID = Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
 
D

Doctor

So should it look like this? This didn't work. I'm not quite sure.

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)


Jeff Boyce said:
I'll guess that your [ContactLastName] field is a text-type field. Maybe
you need to "quote" it, along with any other text-type fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Doctor said:
On strContact I keep getting a type mismatch error and I'm not sure why.
Below I pasted the code performing the DLookup and also the SQL for the query
the DLookup is searching. Me.ContactChurch is a combo box in the form and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone from
the same church exists with the same last name and if so it asks the user if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState, strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value And " [ContactLastName]=" & Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn", "[ContactID]=" &
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID = Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
Click to expand...
 
J

Jeff Boyce

What happens when you try that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Doctor said:
So should it look like this? This didn't work. I'm not quite sure.

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]="
&
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)


Jeff Boyce said:
I'll guess that your [ContactLastName] field is a text-type field. Maybe
you need to "quote" it, along with any other text-type fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Doctor said:
On strContact I keep getting a type mismatch error and I'm not sure
why.
Below I pasted the code performing the DLookup and also the SQL for the query
the DLookup is searching. Me.ContactChurch is a combo box in the form
and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone from
the same church exists with the same last name and if so it asks the
user if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState,
strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn",
"[ChurchID]=" &
Me.ContactChurch.Value And " [ContactLastName]=" & Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn",
"[ContactID]=" &
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn",
"[ContactID]=" &
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID = Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

Same error message as before: Type Mismatch.

Jeff Boyce said:
What happens when you try that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Doctor said:
So should it look like this? This didn't work. I'm not quite sure.

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]="
&
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)


Jeff Boyce said:
I'll guess that your [ContactLastName] field is a text-type field. Maybe
you need to "quote" it, along with any other text-type fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

On strContact I keep getting a type mismatch error and I'm not sure
why.
Below I pasted the code performing the DLookup and also the SQL for the
query
the DLookup is searching. Me.ContactChurch is a combo box in the form
and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone
from
the same church exists with the same last name and if so it asks the
user
if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState,
strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn",
"[ChurchID]="
&
Me.ContactChurch.Value And " [ContactLastName]=" &
Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last
name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1,
Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID =
Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1,
Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

Thanks for the tip about the strings. Yes they did need to all be strings.

As far as strContact, I believe that you posted exactly what I already have
in my string. Perhaps you copied and pasted without making the changes? I've
done that before.
 
S

Steve in MN

I think you need brackets around the field name in the Dlookup.

Like

Dim strlookup As String

strlookup = DLookup("[Pick Date]", "qrystartupcheck1")


I was having the same issue as you.



Doctor said:
Same error message as before: Type Mismatch.

Jeff Boyce said:
What happens when you try that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Doctor said:
So should it look like this? This didn't work. I'm not quite sure.

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]="
&
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)


:

I'll guess that your [ContactLastName] field is a text-type field. Maybe
you need to "quote" it, along with any other text-type fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

On strContact I keep getting a type mismatch error and I'm not sure
why.
Below I pasted the code performing the DLookup and also the SQL for the
query
the DLookup is searching. Me.ContactChurch is a combo box in the form
and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if someone
from
the same church exists with the same last name and if so it asks the
user
if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState,
strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn",
"[ChurchID]="
&
Me.ContactChurch.Value And " [ContactLastName]=" &
Me.ContactLastName.Value)
strAddress1 = DLookup("ContactAddress1", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strAddress2 = DLookup("ContactAddress2", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same last
name.
Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1,
Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID =
Contacts.ContactChurch
GROUP BY Contacts.ContactLastName, Contacts.ContactID, Church.ChurchID,
Contacts.ContactChurch, Contacts.ContactAddress1,
Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

I made some changes and got a different error. But I think that I'm on the
right track.

New String*******************
strContact = DLookup("[ContactID]", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value & "' And [ContactLastName]='" &
Me.ContactLastName.Value & "'")

Error Message******************
Run time error 3075: Syntax error missing operator in query expression
'[ChurchID]=13230' AND [ContactLastName]='cash".
 
M

Mike Painter

Why don't you open a recordset with a quesry rather than use a bunch of
dLookups?
Same error message as before: Type Mismatch.

Jeff Boyce said:
What happens when you try that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Doctor said:
So should it look like this? This didn't work. I'm not quite sure.

strContact = DLookup("ContactID", "qryLLCAddressFillIn",
"[ChurchID]=" &
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)


:

I'll guess that your [ContactLastName] field is a text-type field.
Maybe you need to "quote" it, along with any other text-type
fields.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

On strContact I keep getting a type mismatch error and I'm not
sure why.
Below I pasted the code performing the DLookup and also the SQL
for the query the DLookup is searching. Me.ContactChurch is a
combo box in the form and
Me.ContactLastName is a text box in the form.

What the code is doing on frmContactAdd is searching to see if
someone from the same church exists with the same last name and
if so it asks the user
if
they want to add that persons information into the form.

Thanks in advance.

FORM CODE
Dim strContact As String
Dim strMsg As String
Dim strAddress1, strAddress2, strCity, strStateID, strState,
strZip,
strPhone As String

strContact = DLookup("ContactID", "qryLLCAddressFillIn",
"[ChurchID]="
&
Me.ContactChurch.Value And " [ContactLastName]=" &
Me.ContactLastName.Value) strAddress1 =
DLookup("ContactAddress1", "qryLLCAddressFillIn", "[ContactID]="
& strContact) strAddress2 = DLookup("ContactAddress2",
"qryLLCAddressFillIn", "[ContactID]=" & strContact)
strCity = DLookup("ContactCity", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)
strStateID = DLookup("ContactState", "qryLLCAddressFillIn",
"[ContactID]=" & strContact)
strPhone = DLookup("ContactZip", "qryLLCAddressFillIn",
"[ContactID]="
&
strContact)

strState = DLookup("StateAbb", "State", "[ID]=" & strStateID)

strMsg = "A contact was found from this church with the same
last name. Chr(13) Chr(13)" & _
"Would you like to use their address? Chr(13) Chr(13)" & _
"" & strAddress1 & "Chr(13)" & _
"" & strCity & ", " & strState & " " & strZip & "Chr(13)" & _
"" & strPhone & ""

Debug.Print strContact
If Not IsNothing(strContact) Then
If MsgBox(strMsg, vbYesNo, "Add Address") = vbYes Then
Me.ContactAddress1.Value = strAddress1
Me.ContactAddress2.Value = strAddress2
Me.ContactCity.Value = strCity
Me.ContactState.Value = strStateID
Me.ContactZip.Value = strZip

Me.ContactEmail.SetFocus
End If
End If



SQL CODE
SELECT Contacts.ContactLastName, Contacts.ContactID,
Church.ChurchID, Contacts.ContactChurch,
Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
FROM Church INNER JOIN Contacts ON Church.ChurchID =
Contacts.ContactChurch GROUP BY Contacts.ContactLastName,
Contacts.ContactID, Church.ChurchID, Contacts.ContactChurch,
Contacts.ContactAddress1, Contacts.ContactAddress2,
Contacts.ContactCity, Contacts.ContactState, Contacts.ContactZip,
Contacts.ContactPhone
HAVING (((Contacts.ContactAddress1) Is Not Null))
ORDER BY Contacts.ContactLastName;
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

You have an extra single quote (') in the string. There should not be a
single quote after the 13230: (from your error message)

[ChurchID]=13230' AND [ContactLastName]='cash".

Remove the single quote before the "AND".

Try this:

New String*******************
strContact = DLookup("[ContactID]", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch & " And [ContactLastName]='" &
Me.ContactLastName & "'")


Like Mike Painter said, it would be easier (faster,better?) to open a
recordset. Or use the ELookUp() function at:

http://allenbrowne.com/ser-42.html


Did you know that you don't have to type: Me.ContactChurch.Value ? You can
just use Me.ContactChurch since Value is the default property for (most)
controls. Saves some typing and is easier to read. (IMO)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Doctor said:
I made some changes and got a different error. But I think that I'm on the
right track.

New String*******************
strContact = DLookup("[ContactID]", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value & "' And [ContactLastName]='" &
Me.ContactLastName.Value & "'")

Error Message******************
Run time error 3075: Syntax error missing operator in query expression
'[ChurchID]=13230' AND [ContactLastName]='cash".

Linq Adams via AccessMonster.com said:
These things are always a hairball for me, getting the quotes correct, so I
keep a template to use. Try this:

strContact = DLookup("ContactID", "qryLLCAddressFillIn", "[ChurchID]=" &
Me.ContactChurch.Value And """[ContactLastName]=""" & """
Me.ContactLastName.Value """)

Also, be aware that in the statement

Dim strAddress1, strAddress2, strCity, strStateID, strState, strZip,
strPhone As String

only

strPhone

is being Dimmed as a String. Every other variable is being dimmed as Variant,
which is the default. To have them all declared as String you need to use

Dim strAddress1 As String, strAddress2 As String, strCity As String,
strStateID As String, strState As String, strZip As String, strPhone As
String

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Top