DLookup 1

[cneill]

Hi,

I would like to combine a numeric and text field together

The following works

DLookup("[Scheme]", "QrySchemeValidation", "[Scheme]=" & Me.[Schemes] & Me.[Phase])

Both Schemes and Phase are numeric fields, I would like to add a text field, but this does not work, I am getting a "missing operator" I believe this is due mixing Numeric and Text field. What can I to change the following so it works

DLookup("[Scheme]", "QrySchemeValidation", "[Scheme]=" & Me.[Schemes] & Me.[Phase]& Me.[PhaseDiscription])

Any help would be much appreciated
Thanks
CNeill

 

[TheAceMan1]

How are ya cneill . . .

. . . and this

[blue]"[Scheme]=" & Me.[Schemes] & Me.[Phase] & "'"Me.[PhaseDiscription]"'"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

I guess you wanted something like this:

DLookup("Scheme", "QrySchemeValidation", "Scheme=" & Me!Schemes & " AND Phase=" & Me!Phase & " AND PhaseDiscription='" Me!PhaseDiscription & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

cneill . . .

Correction:

[blue]"[Scheme]=" & Me.[Schemes] & Me.[Phase] & "'" & Me.[PhaseDiscription] "'"[/blue]

Note I'm only showing the criteria!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi AceMan1,

Still not working, just does not like it still tells me there is a missing operator,

Any thoughts?

Hi PHV
No Joy either
any thoughts

Thanks
cneill

 

[PHV]

any thoughts
Need more info.
Where is located your DLookUp call ?
What are the fields returned by QrySchemeValidation ?
What is Me ?
What is the meaning of No Joy either ?
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cneill]

Hi PHV thanks for your help

Where is located your DLookUp call ?
Is located in the Save event on a Popup form called FrmAddNewScheme

What are the fields returned by QrySchemeValidation ?
This is the sql of the Query
SELECT tblAccountSchemes.SiteID, ([tblAccountSchemes]![SchemeID] & [tblAccountSchemes]![PhaseID] & [tblAccountSchemes]![SocialSchemeDiscription]) AS Scheme
FROM tblAccountSchemes
WHERE (((tblAccountSchemes.SiteID)=[Forms]![FrmSchemes]![SiteID]));

What is Me ?
Me is the popup Form called FrmAddNewScheme
This form contains the Unbound combo boxs called
Schemes, Phase and PhaseDiscription, the user selects the three combo box options.
So on the save button (FrmAddNewScheme) the query collects the unique SiteID from the main form called FrmSchemes
This filters the SiteID records the one I need, each SiteID can have any number of SchemeID's attached to it. Each SchemeID can also have a different PhaseID attached to it and again could have a different Phase Discription attached to the PhaseID.
So the Query joins the three elements together in a field called Scheme. This result needs to be checked against the three selections made on the from FrmAddNewScheme. if there is a match the user is told to change the selection, if no match then the information is saved to the tblAccountSchemes as follows
A new Record is created in the tblAccountSchemes
SiteID is saved to SiteID - numeric number
Scheme is saved to SchemeID - numeric number
Phase is saved to PhaseID - numeric number
PhaseDiscription is saved to Discription - text

What is the meaning of No Joy either ?
I just ment your way of doing the dlooup was different to Aceman1 which also did not work.

I hope this makes sence
any questions please fire back
Thanks
cneill

 

[cneill]

Hi PHV,

Just thought that the PhaseDiscription needs to be able to handle a mixture of numeric and Text as it is a text field

Thanks
cneill

 

[shannonp]

Very similar to Acemans suggestion but I'm enclosing all 3 fields (Schemes, Phase and PhaseDiscription) with quotations rather than just PhaseDiscription. However, this assumes QrySchemeValidation.Scheme is textual.

DLookup("Scheme", "QrySchemeValidation", "Scheme='" & Me.[Schemes] & Me.[Phase] & Me.[PhaseDiscription] & "'")

*Note if declaring your dlookup statement directly into the Control Source of a textbox on a form, the "Me." keyword will need to be ommitted from dlookup.

=DLookup("Scheme", "QrySchemeValidation", "Scheme='" & [Schemes] & [Phase] & [PhaseDiscription] & "'")

Shann

 

[TheAceMan1]

cneill . . .

Yes ... there is a missing operator!

[blue]"[Scheme]=" & Me.[Schemes] & Me.[Phase] & "'" & Me.[PhaseDiscription] [red][b]&[/b][/red] "'"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi TheAceMan1 and Shannonp

I have tried both options

If DLookup("Scheme", "QrySchemeValidation", "Scheme='" & Me.[Schemes] & Me.[Phase] & Me.[PhaseDiscription] & "'") Then ......

This give me Type Mismatch

If DLookup("Scheme", "QrySchemeValidation", "[Scheme]=" & Me.[Schemes] & Me.[Phase] & "'" & Me.[PhaseDiscription] & "'") Then .....

This gives me Syntax error(missing operator) in query expression '[scheme]=461'test"
46 being the SchemeID, 1 is the PhaseID and Test is the PhaseDiscription.

Any thoughts!!
Thanks

CNeill

 

[PHV]

I'd try something like this:

If IsNull(DLookup("Scheme", "QrySchemeValidation", "Scheme='" & Me!Schemes & Me!Phase & Me!PhaseDiscription & "'")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cneill]

Hi PHV,

This works, with a slight bit of code change to other parts of the save event.

Thanks for everyones help

cneill

 

[Remou]

Hi cneill
A good way to thank someone for a helpful post or solution is to click the link above that says, for example:

Thank PHV
for this valuable post!

This will mark the post with a star, which is also useful for people looking for an answer to a similar problem.

http://lessthandot.com