query string error in format statement

  • Thread starter Thread starter ABL
  • Start date Start date
A

ABL

Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
 
Sorry about that, I apparently hit the wrong key!

Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], "0000") & "-" &
IIf(IsNull([product].[length]),"00",IIf(Val([product].[length])=Rnd(Val([pro
duct].[length])),Format$(Val([product].[length]),"00"),Format(Rnd(Val([produ
ct].[length])*10),"00"))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"

I am getting an "Error Expected End of Statement" at the "0000".

Can anyone help me please?

I am trying to build a string consisting of a single char category_id,
single char factor_id, a 4-digit formatted style_no, a "-" and a 2-digit
formatted length (which is a string).

Thanks,
Alden
 
The error is occurring because you're trying to embed " characters as part
of the text string, but ACCESS is reading those " characters as the end of
the text string instead.

Double up the " characters that are inside the text string and ACCESS will
interpret the "" as ":

item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], ""0000"") & ""-"" &
IIf(IsNull([product].[length]),""00"",IIf(Val([product].[length])=Rnd(Val([p
ro
duct].[length])),Format$(Val([product].[length]),""00""),Format(Rnd(Val([pro
du
ct].[length])*10),""00""))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"


--

Ken Snell
<MS ACCESS MVP>

ABL said:
Sorry about that, I apparently hit the wrong key!

Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], "0000") & "-" &
IIf(IsNull([product].[length]),"00",IIf(Val([product].[length])=Rnd(Val([product].[length])),Format$(Val([product].[length]),"00"),Format(Rnd(Val([produ
ct].[length])*10),"00"))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"

I am getting an "Error Expected End of Statement" at the "0000".

Can anyone help me please?

I am trying to build a string consisting of a single char category_id,
single char factor_id, a 4-digit formatted style_no, a "-" and a 2-digit
formatted length (which is a string).

Thanks,
Alden

ABL said:
Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
Click to expand...
Click to expand...
 
Thank you so much! I was hoping it was something easy (of course, if it
were truly "easy," it might have occurred to me).
Ken Snell said:
The error is occurring because you're trying to embed " characters as part
of the text string, but ACCESS is reading those " characters as the end of
the text string instead.

Double up the " characters that are inside the text string and ACCESS will
interpret the "" as ":

item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], ""0000"") & ""-"" &
IIf(IsNull([product].[length]),""00"",IIf(Val([product].[length])=Rnd(Val([p
ro
Click to expand...
duct].[length])),Format$(Val([product].[length]),""00""),Format(Rnd(Val([pro
du
ct].[length])*10),""00""))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"


--

Ken Snell
<MS ACCESS MVP>

ABL said:
Sorry about that, I apparently hit the wrong key!

Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], "0000") & "-" &
Click to expand...
IIf(IsNull([product].[length]),"00",IIf(Val([product].[length])=Rnd(Val([product].[length])),Format$(Val([product].[length]),"00"),Format(Rnd(Val([produ
ct].[length])*10),"00"))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"

I am getting an "Error Expected End of Statement" at the "0000".

Can anyone help me please?

I am trying to build a string consisting of a single char category_id,
single char factor_id, a 4-digit formatted style_no, a "-" and a 2-digit
formatted length (which is a string).

Thanks,
Alden

ABL said:
Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
Click to expand...
Click to expand...
Click to expand...
 
The double-quote in front of the 0000 is matched to the double-quote before
'select' and Access VBA think that the end of the String for the RowSource
and there should not be anything after.

When you need a double-quote in a double-quote-delimited String, you need to
use *two* double-quotes. Try:

item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], ""0000"") & ""-"" &
IIf(IsNull([product].[length]),""00"",IIf(Val([product].[length])=Rnd(Val([p
ro
duct].[length])),Format$(Val([product].[length]),""00""),Format(Rnd(Val([pro
duct].[length])*10),""00""))) AS style_str " & _
" FROM product WHERE product.vendor_id = 2;"


--
HTH
Van T. Dinh
MVP (Access)




ABL said:
Sorry about that, I apparently hit the wrong key!

Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
item_list.RowSource = "select [product].[category_id] &
[product].[factor_id] & format([product].[RCI_style], "0000") & "-" &
IIf(IsNull([product].[length]),"00",IIf(Val([product].[length])=Rnd(Val([product].[length])),Format$(Val([product].[length]),"00"),Format(Rnd(Val([produ
ct].[length])*10),"00"))) AS style_str " _
& "FROM product WHERE product.vendor_id = 2;"

I am getting an "Error Expected End of Statement" at the "0000".

Can anyone help me please?

I am trying to build a string consisting of a single char category_id,
single char factor_id, a 4-digit formatted style_no, a "-" and a 2-digit
formatted length (which is a string).

Thanks,
Alden

ABL said:
Basically, I have a string in a query (used as rowsource for a list box).
I've pared it down to try to find the error. Right now, I've got:
Click to expand...
Click to expand...
 
Back
Top