VBA run sql criteria

[raffie77]

Hello people,

I have a question


I have a table with financial bank records
I've made a query for example:
filter all records where accountnummber = "000001" or 000002 or 0000003 or etc etc
and I made a custom field called "type"
if it matches one of those accountnumbers the type = "budget 1"
it works fine,
but I have like 10 queries and all those queries has lots of criteria s (or or or etc)
is it possible to create a table where I put all query criteria s
and do a docmd.runsql?
I store the new transactions in a temporary table zo I can adjust the records and after that I merge the new records

do I need to do something like?
SELECT * from temptable where accountnumber =....
do while loop look at the table or something?
I want the criterias in a table and let vba look at that table and finish the SQL

I hope I make sence


Ralph

[maximus]

Tried to replicate your problem. I have a very single Table with two Fields
1) AccountNumber
2) Type

Objective:

1) Want to assign value to field Type depending on the value of field Account Number.


I have used a simple DAO.Recordset here with a combination of Do While Loop and a Select Case. All conditions are specified in the Select case which I have used to update the data in my table.

Private Sub Command0_Click()
Dim rst As DAO.Recordset


Set rst = CurrentDb.OpenRecordset("Table1")
Do While Not rst.EOF
rst.Edit
Select Case rst!AccountNumber
Case Is = "0001"
rst!Type = "Type1"
Case Is = "0002"
rst!Type = "Type2"
Case Is = "0003"
rst!Type = "Type3"
Case Else
rst!Type = ""
End Select
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

Mark the thread solved if your problem is solved.

[raffie77]

thanks a lot maximus

I will take a look at that.
at least I have a head start, I now know where to start.

thanks a lot


Ralph