Dlookup question 1

[fordboy0]

Hey all,

I'm coming over from the VB side, and I have little experience with databases so be gentle.

I've got a NASCAR pool of which I'm the "commissioner". I figured the best way for me to figure out how this database stuff works is to have a hands on project to get my feet wet. Now, I won't mention my issues with relationships and querys and all that other jazz...

I'm trying to figure out why the following lines of code work / don't work.

The fields referenced in the dlookup are as follows:
Tablename = tblCarNumbers
CarID = PrimaryKey / Long Autonumber
Car Number = String

Dim VarX as variant
...

'This works
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] = Forms!frmcardriver.txtCarNumber.Text&quot

'This does not
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] =" & Forms!frmcardriver.txtCarNumber.Text)

'Nor does this
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] = 43&quot

'Or this
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] =" & "43&quot

'Or even this
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] =" & 43)

All except the first one gives me a type-mismatch.

Am I just a total idiot or what (don't answer that). Any thoughts?

 

[scriverb]

From your posting it appears that the table field [car number] is of the string/text type. The first example of DLookup makes a comparison between a Text type field and a text type form control.

The other examples all finally appear to access as a Text type field comparison to an Integer:

[car number] = 43 Type Mismatch Error!!!

To make them each work use the following:
VarX = DLookup("[carid]", "tblCarNumbers", "[car number] = '43'&quot

VarX = DLookup("[carid]", "tblCarNumbers", "[car number] =" & "'43'&quot

VarX = DLookup("[carid]", "tblCarNumbers", "[car number] =" & "'43'&quot

The resulting comparison would all look like this:
[car number] = '43'

This would work as it is a text to text/string comparison. No type mismatch.
Bob Scriver

 

[GComyn]

fordboy0,

You can use the single quotes to deliniate the strings within double quotes. however, it's not that easy to read.

Taking the line you say works, and fixing it as such:

VarX = DLookup("[carid]", "tblCarNumbers", "[car number]='" & Forms!frmcardriver.txtCarNumber.Text & "'")

notice the single quotes "around" the form reference.

Now, what I do, so I can tell where the string variables are (and make sure that any single quotes within the variable don't mess things up), is to have a constant set up with the double qoutes to use around string variables within strings....

Here is the syntax:

Const DblQuote as String = """"

Put that up in the declaration section, and you can use it in any function/sub in the module.

Now here is how it would look with the constant:

VarX = DLookup("[carid]", "tblCarNumbers", "[car number]=" & dblquote & Forms!frmcardriver.txtCarNumber.Text & dblquote)

Hope that helps!

GComyn

 

[Parax]

OR

VarX = DLookup("[carid]", "tblCarNumbers", "[car number]=" & chr(34) & Forms!frmcardriver.txtCarNumber.Text & chr(34))