Help with IF statement

[voskouee]

Beginners help!

How can i write in VB an IF statement that will do the following action

IF

value of field1 = value of field 3

then move this row to this table
?

is this possible? can i move lines based on these criteria?

 

[ColinEssex]

You'll need to Do Loop / MoveFirst, MoveNext etc through the recordset and compare each value, then do an Edit to delete the record.

Check out Do Loop / MoveFirst / MoveNext and Edit in Access help. It tells you how to do it there.

Col

 

[voskouee]

Let me explain further what am doing..

i am calling a query in my sub

Public Sub Find_Couyntries()

DoCmd.OpenQuery "query3"

after this i need to check two fields.

lets say field1 and field 2

if field 1 = field 2 then move row in this table X
if field 1 different than field 2 move row to table Y

this is done with recordset functions?

 

[Bat17]

you should be able to do this with standard Append queries,
Create one that will copy all the data that matches, another to do the other table with no match and then a Delete query to remove the data from the original table.
you caan then run these queries from code which should be quicker than using recordsets.
Peter

 

[voskouee]

the problem is that the unmatched items are not identical so i have to do more queries on those.. the fields that i will use in the if statement are static. i have 12 different values and i want to use them in an if statement.. can u help me write the if statement?

 

[Bat17]

if you are trying to move data to more than one table you will need a seperate query for each table involved.
you can add multiple criteria to a single query to allow more than one match at a time.

I would need more info to be able to work out how best to do it though.

Peter

 

[voskouee]

I am attaching a spreadsheet with two sheets.
on the first are the results from query that have matching fields

Query2.SOBP and Query1.SOBP

and the other one

Query2.SOBO and Query1.SOBP

Have different values.

On the first case i want to append the entries in a table i have already created with the name of the field. in this case is "RN00144GE3"

and on the other case where the two fileds are not the same..
i want to start append on each table that is found there. I remind u all fields in that row have identical tables..
so i have to append in tables
RN00736736
RN00713713
RN00900900
RN00144GM3
RN00900900
RN00144GM3

so i seperate the entries.. these numbers are my table names also. i ve created them before. and they 24 statci ones..

is it clear?

 

[Bat17]

Are you saying that you want to save each row to a table that matches the SOBP field in that Row?

peter

 

[Bat17]

lets see if this gets us started
I have only done mismatch for now.
Assumption:-
If Query1.SOBP does not match Query2.SOBP then append that record to a table with the same name as Query1.SOBP

Dim rs As DAO.Recordset
Dim strSQL As String
' mismatched
DoCmd.SetWarnings False
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT query3.[Query1.SOBP] FROM query3 WHERE (((query3.[Query1.SOBP])<>[Query2.SOBP]));")
Do While Not rs.EOF
    strSQL = "INSERT INTO " & rs![Query1.SOBP] & " SELECT query3.* FROM query3 WHERE (((query3.[Query2.SOBP])<>'" & rs![Query1.SOBP] & "') AND ((query3.[Query1.SOBP])='" & rs![Query1.SOBP] & "'));"
    DoCmd.RunSQL (strSQL)
    rs.MoveNext
Loop
DoCmd.SetWarnings True

Peter

 

[voskouee]

Thanks!

Thank you so much i will give it a try to see how it works..

thanks so much again

 

[voskouee]

Error

When i try to run the code you suggested i get this error and i dont know why..

the Date field is not on the destination table.

 

[Bat17]

The code is appending all of the fields in query3 to a table, it looks as if the table does not have a field 'Date' to match the query.


Peter

ps. Date is not a good field name as it clashes with the Access Date() function and can cause problems.

 

[voskouee]

how can i change the code you gave me to append to a table with different structure.. can i indicate which field to which to be appended?

if you see in the excel spreadsheet i am attaching this is the new table am appending to.

thanks

 

[Bat17]

I dont have time to go through it all at the moment but this should give you the idea. You need to list all of the fields that you want to use in the table then list the matching fields from the query. use this strSQL to replace the StrSQL line in the original code

 ' first list fields to insert into
strSQL = "INSERT INTO " & rs![Query1#SOBP] & " ( [Date], [Org], [FACCT], [CSUB], [P/S] )"
' then list the field to use from the query (in same order )
strSQL = strSQL + " SELECT query3.[Query1.Date], query3.[Query1.Org], query3.[Query1.FACCT], query3.[Query1.CSUB], query3.[Query1.P/S]"
' this line should be ok
strSQL = strSQL + " FROM query3 WHERE (((query3.[Query2#SOBP])<>'" & rs![Query1#SOBP] & "') AND ((query3.[Query1#SOBP])='" & rs![Query1#SOBP] & "'));"

HTh

Peter