Dlookup question

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Can anybody tell me what's wrong with this:

Dim MVIN As String
MVIN = MID(Forms!Accident.Vehicles.Form.VIN, 10, 1)

Me.Year = DLookup("str([Year])", "YearCode", "[VINCODE] = " & MVIN)

I am getting a data mismatch error?

Thanks in advance.

Bonnie
 
Can anybody tell me what's wrong with this:

Dim MVIN As String
MVIN = MID(Forms!Accident.Vehicles.Form.VIN, 10, 1)

Me.Year = DLookup("str([Year])", "YearCode", "[VINCODE] = " & MVIN)

I am getting a data mismatch error?

Thanks in advance.

Bonnie
Click to expand...

I have no idea what you are trying to do here.

1) Your mismatch is in the Where clause.
MVIN is a string, therefore it needs to be enclosed within single
quotes.

"[VINCODE] = '" & MVIN & "'")

2) I have no idea why you are looking up str([Year]).
DLookUp returns a field value. I don't think you can convert it within
the LookUp itself (I haven't tested this so I may be wrong), though
you could after you have the value.
= Cstr(DLookUp(" etc ..."))

3) Year is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
 
Presumably VINCODE is a text field, which means the value needs to be in
quotes:

Me.Year = DLookup("str([Year])", "YearCode", "[VINCODE] = '" & MVIN & "'")

Exagerated for clarity, that's

Me.Year = DLookup("str([Year])", "YearCode", "[VINCODE] = ' " & MVIN & " '
")

You should rename whatever Me.Year is point to: Year is a reserved word, and
you should never use it for your own purposes. For a good discussion on what
names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
 
Back
Top