Simple String..not simple? 1

[ThatNeverWorks]

I have an Order Table where the "In" field is ether 1= Outstanding or 0=Arrived

I like to run a Query on Part Numbers of Parts on order, witch are still outstanding.

But all mutations on the SQL syntax give me grief.

Any pointers?

Regards


My String is like thisnot a jocke, lol)



gsqlstr = ""
gsqlstr = "SELECT * FROM OrderTBL"
gsqlstr = gsqlstr & " where "
gsqlstr = gsqlstr & "PartNo = " & "'" & PartStoreNo_txt & "'"
gsqlstr = gsqlstr & "AND"
gsqlstr = gsqlstr & "In = " & "1"
DataOrder.RecordSource = gsqlstr
DataOrder.Refresh

 

[MarkSweetland]

[red]IN[/red] is a SQL reserved word and shouldn't be used as a Column name in a Table. Change the column name to something else.

[red]IN[/red] is used:

Select * from tableName where partNum [red]IN[/red] ('P1234', 'P1334', 'P5432')

which gives you all records with any of the listed part numbers (also could be written: WHERE partNum='P1234' OR partNum = 'P1334' ... etc)





Mark

 

[Guest_imported]

Hi Mark

Just changed the "In" to "Test"...

Still falls over


gsqlstr = ""
gsqlstr = "SELECT * FROM OrderTBL"
gsqlstr = gsqlstr & " where "
gsqlstr = gsqlstr & "PartNo = " & "'" & PartStoreNo_txt & "'"
gsqlstr = gsqlstr & "AND"
gsqlstr = gsqlstr & "Test = " & "'" & Text2_txt & "'"


No go

Run Time error 3075

Syntax error (missing operator) in Query expression
'PartNo='A-30'ANDTest='1".


A-30 is the content of textbox PartNo_txt and 1 the contend of text2_txt.

I try since 3 days to get this going..

many thx for your help any way..

best regards

 

[MarkSweetland]

Need a space before and after the AND in your statement.

gsqlstr = gsqlstr & " AND "

SQL's very picky on separation of keywords.

Mark

 

[ThatNeverWorks]

THX Mark...
The code runs, but will not come up with the Goods.

It stops with the first Record of the Table that matches the Part No, ignoring the second criteria of the query.

It shows the right Par, but this entry is long booked in to stock and not Outstaending no more.

I use the "1" in the table just as a "Flag" to know wicht ordered part is still on order.

After the Part arrived this get changed to "0" und the record of this order becomes History.

So all Orders stay in the same table, i will have the same part No. many times over, but only once or twice on order ae Flagt with the "1".

My Query stops at the first record that fits the Part No. but ignores the "1" bit.

I am realy stuck here..

sorry for all the agro..

regards

 

[MarkSweetland]

If the "flag" column is an integer; e.g., 0 or 1 then you shouldn't enclose the second part of the query in single quotes...

gsqlstr = "" ' redundant, but ok...
gsqlstr = "SELECT * FROM OrderTBL"
gsqlstr = gsqlstr & " where "
gsqlstr = gsqlstr & "PartNo = " & "'" & PartStoreNo_txt & "'"
gsqlstr = gsqlstr & "AND"
gsqlstr = gsqlstr & "Test = " & Text2_txt



Mark

 

[ThatNeverWorks]

Got it crackt now...

You where 100% right..

I changed the "Flag" to "Y" and "N" and it flys.

Now I know the why I got all the "Data Mismatch" errors.

THX for all your great help..

best regards