UPDATE TOP records WHERE and ORDERED? 1

[hovercraft]

I'm receiving a syntax error with the following sql

UPDATE tmp_tbl_letters SET tmp_tbl_letters.my_print = True
WHERE tmp_tbl_letters.my_id In(SELECT DISTINCT TOP 100 FROM tmp_tbl_letters WHERE tmp_tbl_letters.my_state="oh" AND tmp_tbl_letters.my_city="columbus" ORDER BY my_date);

I would like to use this in vba so that the number of records returned by TOP will be a variable as well as the field my_state and my_city will also be variables all in a loop...but first I'm struggling with the proper syntax.

Could someone please assist with my SQL.

Thanks in advance,
Hovercraft

 

[maxhugen]

Try:

SELECT DISTINCT TOP 100 [red]my_id[/red] FROM tmp_tbl_letters WHERE tmp_tbl_letters.my_state="oh" AND tmp_tbl_letters.my_city="columbus" ORDER BY my_date

Max Hugen
Australia

 

[hovercraft]

PERFECT!! That was it.
I did have to change "DISTINCT" to "DISTINCTROW" due to an error "my_date conflicts with DISTINCT" but other than that it's a beautiful thing!

Thank you so much Max. I spent more than a few hours on this.

Hovercraft

 

[maxhugen]

No probs! Have a good one.

Max Hugen
Australia

 

[hovercraft]

well heck, I guess I spoke too soon.

It worked great within the query builder but when I moved it to vba I receive a syntax error again.

 DoCmd.RunSQL ("UPDATE tmp_tbl_letters SET tmp_tbl_letters.my_print = True WHERE tmp_tbl_letters.my_ltr_id In(SELECT DISTINCTROW TOP " & "'" & my_quant & "'" & " my_ltr_id FROM tmp_tbl_letters WHERE tmp_tbl_letters.TicketState = " & "'" & my_st & "'" & " AND tmp_tbl_letters.TicketCounty = " & "'" & my_county & "'" & " ORDER BY tmp_tbl_letters.TicketDate )")

Could it be that because of the nested query that I can't use RunSQL ? or perhaps I've got single quotes in the wrong place?

 

[maxhugen]

Ah, another couple of gotchas!

1. Remove the parenthesis surrounding the entire statement

2. my_quant is numeric, not text, so it should not be enclosed by your single quotes.

A handy tip: assign your statement to a variable, then run that:

Dim sSQL$

    sSQL = "UPDATE...   "
    DoCmd.RunSQL sSQL

This allows you to break at the Docmd line, and check sSQL in the Immediate window for errors - or enter it into a query and try to run it.

Max Hugen
Australia

 

[hovercraft]

Double-Stars for you Max!

That was exactly the issue, my_quant is numeric.

I will take your advice and start putting my sql in a string and then use runsql