dlookup problem

[Guest]

Hi
I have a subform which is to make an invoice to add products (granite slabs)
to.

Default data is to be drawn from an existing stocktake file, but should be
able to be changed.

The ID field is a combo box, this is what i have at the moment

Private Sub ID_AfterUpdate()
[Forms]![Invoices].Recalc
CustomLength = DLookup("Length", "Stocktake", "[ID]=" & ID)
CustomWidth = DLookup("Width", "Stocktake", "[ID]=" & ID)
Colour = DLookup("Colour", "Stocktake", "[ID]=" & ID)
Pricesqm = DLookup("Price", "Prices", "[Colour]= " & Colour)
Sold = True
End Sub

The length and width come up, and that works perfectly, but it won't give me
the price. It keeps coming up with an error for "[Colour]= " & Colour
criteria as follows

Run time error '3075':
Syntax error (missing operator) in query expression '[Colour] = Impala Black
(Tiger Black)'.

Whats wrong?
Any help would really be appreciated.

Thanks
Kody.

 

[James Franklin]

Hi
I have a subform which is to make an invoice to add products (granite slabs)
to.

Default data is to be drawn from an existing stocktake file, but should be
able to be changed.

The ID field is a combo box, this is what i have at the moment

Private Sub ID_AfterUpdate()
[Forms]![Invoices].Recalc
CustomLength = DLookup("Length", "Stocktake", "[ID]=" & ID)
CustomWidth = DLookup("Width", "Stocktake", "[ID]=" & ID)
Colour = DLookup("Colour", "Stocktake", "[ID]=" & ID)
Pricesqm = DLookup("Price", "Prices", "[Colour]= " & Colour)
Sold = True
End Sub

The length and width come up, and that works perfectly, but it won't give me
the price. It keeps coming up with an error for "[Colour]= " & Colour
criteria as follows

Run time error '3075':
Syntax error (missing operator) in query expression '[Colour] = Impala Black
(Tiger Black)'.

Whats wrong?
Any help would really be appreciated.

Thanks
Kody.

The value of Colour has a space in it. Try:
Pricesqm = DLookup("Price", "Prices", "[Colour]= '" & Colour & "'")

 

[fredg]

Hi
I have a subform which is to make an invoice to add products (granite slabs)
to.

Default data is to be drawn from an existing stocktake file, but should be
able to be changed.

The ID field is a combo box, this is what i have at the moment

Private Sub ID_AfterUpdate()
[Forms]![Invoices].Recalc
CustomLength = DLookup("Length", "Stocktake", "[ID]=" & ID)
CustomWidth = DLookup("Width", "Stocktake", "[ID]=" & ID)
Colour = DLookup("Colour", "Stocktake", "[ID]=" & ID)
Pricesqm = DLookup("Price", "Prices", "[Colour]= " & Colour)
Sold = True
End Sub

The length and width come up, and that works perfectly, but it won't give me
the price. It keeps coming up with an error for "[Colour]= " & Colour
criteria as follows

Run time error '3075':
Syntax error (missing operator) in query expression '[Colour] = Impala Black
(Tiger Black)'.

Whats wrong?
Any help would really be appreciated.

Thanks
Kody.

The Datatype of [Colour] appears to be text. The value needs to be
enclosed within single quotes.
Pricesqm = DLookup("Price", "Prices", "[Colour]='" & Colour & "'")

Also ....
Width 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'

Change [Width] to something else, perhaps [SlabWidth]