|
|
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;
|
|
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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
"Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message
news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.com...
[Quoted Text] > 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;
>
|
|
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" wrote:
[Quoted Text] > 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 > http://mvp.support.microsoft.com/> > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/> > "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message > news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.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; > > > > |
|
What happens when you try that?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message
news:F804C16F-093D-46A7-B8B1-90A85E045759[ at ]microsoft.com...
[Quoted Text] > 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" wrote: > >> 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 >> http://mvp.support.microsoft.com/>> >> Microsoft IT Academy Program Mentor >> http://microsoftitacademy.com/>> >> "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message >> news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.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; >> > >> >>
|
|
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
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1
|
|
Same error message as before: Type Mismatch.
"Jeff Boyce" wrote:
[Quoted Text] > What happens when you try that? > > Regards > > Jeff Boyce > Microsoft Office/Access MVP > > "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message > news:F804C16F-093D-46A7-B8B1-90A85E045759[ at ]microsoft.com... > > 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" wrote: > > > >> 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 > >> http://mvp.support.microsoft.com/> >> > >> Microsoft IT Academy Program Mentor > >> http://microsoftitacademy.com/> >> > >> "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message > >> news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.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; > >> > > >> > >> > > > |
|
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.
"Linq Adams via AccessMonster.com" wrote:
[Quoted Text] > 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 > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1> > |
|
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" wrote:
[Quoted Text] > Same error message as before: Type Mismatch. > > "Jeff Boyce" wrote: > > > What happens when you try that? > > > > Regards > > > > Jeff Boyce > > Microsoft Office/Access MVP > > > > "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message > > news:F804C16F-093D-46A7-B8B1-90A85E045759[ at ]microsoft.com... > > > 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" wrote: > > > > > >> 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 > > >> http://mvp.support.microsoft.com/> > >> > > >> Microsoft IT Academy Program Mentor > > >> http://microsoftitacademy.com/> > >> > > >> "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message > > >> news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.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; > > >> > > > >> > > >> > > > > > > |
|
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" wrote:
[Quoted Text] > 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 > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1> > |
|
Why don't you open a recordset with a quesry rather than use a bunch of
dLookups?
Doctor wrote:
[Quoted Text] > Same error message as before: Type Mismatch. > > "Jeff Boyce" wrote: > >> What happens when you try that? >> >> Regards >> >> Jeff Boyce >> Microsoft Office/Access MVP >> >> "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message >> news:F804C16F-093D-46A7-B8B1-90A85E045759[ at ]microsoft.com... >>> 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" wrote: >>> >>>> 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 >>>> http://mvp.support.microsoft.com/>>>> >>>> Microsoft IT Academy Program Mentor >>>> http://microsoftitacademy.com/>>>> >>>> "Doctor" <Doctor[ at ]discussions.microsoft.com> wrote in message >>>> news:E5D2B848-852E-47FD-927E-789EA4E627B7[ at ]microsoft.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;
|
|
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" wrote:
[Quoted Text] > 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" wrote: > > > 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 > > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1> > > > |
|
|