Help with if statement

voskoue · February 5th, 2007, 06:48 AM

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

How can i do this?

mmcdonal · February 5th, 2007, 08:27 AM

Dim rs, rsX, rsY As ADODB.Recordset
Dim sSQL, sSQLX, sSQLY As String
Dim sField1, sField2 As String

'Open Recordset on the table you are checking:
sSQL = "SELECT * FROM tblFirstTable"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table X for Appending
sSQLX = "SELECT * FROM tblX"
Set rsX = New ADODB.Recordset
rs.Open sSQLX, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table Y for Appending
sSQLY = "SELECT * FROM tblX"
Set rsY = New ADODB.Recordset
rs.Open sSQLY, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Check values in first table
If rs.RecordCount <> 0 Then
   Do Until rs.EOF
      sField1 = rs("Field1")
      sField2 = rs("Field1")
         If sField1 = sField2 Then
            rsX.AddNew
            rsX("Field1") = rs("Field1")
            rsX("Field2") = rs("Field2")
            'etc through fields
            rsX.Update
         Else
            rsY.AddNew
            rsY("Field1") = rs("Field1")
            rsY("Field2") = rs("Field2")
            'etc through fields
            rsY.Update
         End If
   rs.MoveNext
   Loop
End If

rsX.Close
rsY.Close
rs.Close

Add that after your query code. Did that help?

mmcdonal

voskoue · February 5th, 2007, 09:01 AM

thank you so much.. but let me go a step further..

if i have more than one tables i open them all?

if field1 is not equal tto field2 there are more than one tables involved.. i wasnt cleared about this from the begining.

if field 1 is ABC move to table ABC
if field 1 is DFR move to table DFR
if field 1 is HGJ move to table HGJ

so i have these 12 fields that have their tables already created and i want to throw rows in them based on that criteria..

mmcdonal · February 5th, 2007, 09:23 AM

I think there might be a fundamental design issue here, but you can do this as well with the code, just open a recordset on each table first.

The issue I have is when do you run this? If you run it once, and then immediately run it a second time, then you will end up with duplicates. Then each time you run it, you will get more duplicates.

I would at least add a Yes/No field in your first table called Appended or something like that, and then when you copy a record over, set the Appended field to Yes. Then your initial sSQL variable would be:

"SELECT * FROM tblFirstTable WHERE [Appended] = 0"

Then:

If rs.RecordCount <> 0 Then
   Do Until rs.EOF
      sField1 = rs("Field1")
      sField2 = rs("Field1")
      rs("Appended") = True
      rs.Update
        If sField1 = sField2 Then
            rsX.AddNew
            rsX("Field1") = rs("Field1")
            rsX("Field2") = rs("Field2")
            'etc through fields
            rsX.Update
         Else
            rsY.AddNew
            rsY("Field1") = rs("Field1")
            rsY("Field2") = rs("Field2")
            'etc through fields
            rsY.Update
         End If
   rs.MoveNext
   Loop
End If

HTH

mmcdonal

voskoue · February 5th, 2007, 09:38 AM

Ok i will add the append field as u suggested.

basically from the table i have to use the query i did and based on those results throw rows in the tables named after the fields.. i will try the code u gave me.

one more question. this:
'Open Recordset on the table you are checking:
sSQL = "SELECT * FROM tblFirstTable"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

is actually a query and not a table.. i ve already opened the query with this command

DoCmd.OpenQuery "query3"

do i have to do it again?

mmcdonal · February 5th, 2007, 09:41 AM

sSQL = "SELECT * FROM query3"

Remove the DoCmd.OpenQuery from your code.

mmcdonal

voskoue · February 5th, 2007, 10:05 AM

Dim rs, rsRN00144GE5, rsRN00144GM3, rsRN00713713, rsRN00736736, rsRN00900900 As ADODB.Recordset
Dim sSQL, sSQLRN00144GE5, sSQLRN00144GM3, sSQLRN00713713, sSQLRN00736736, sSQLRN00900900 As String
Dim sField1, sField2 As String

'Open Recordset on the table you are checking:
sSQL = "SELECT * FROM query3"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00144GE5 for Appending
sSQLRN00144GE5 = "SELECT * FROM RN00144GE5"
Set rsRN00144GE5 = New ADODB.Recordset
rs.Open sSQLRN00144GE5, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00144GM3 for Appending
sSQLRN00144GM3 = "SELECT * FROM RN00144GM3"
Set rsRN00144GM3 = New ADODB.Recordset
rs.Open sSQLRN00144GM3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00713713 for Appending
sSQLRN00713713 = "SELECT * FROM RN00713713"
Set rsRN00713713 = New ADODB.Recordset
rs.Open sSQLRN00713713, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00736736 for Appending
sSQLRN00736736 = "SELECT * FROM RN00736736"
Set rsRN00736736 = New ADODB.Recordset
rs.Open sSQLRN00736736, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00736736 for Appending
sSQLRN00736736 = "SELECT * FROM RN00736736"
Set rsRN00736736 = New ADODB.Recordset
rs.Open sSQLRN00736736, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00900900 for Appending
sSQLRN00900900 = "SELECT * FROM RN00900900"
Set rsRN00900900 = New ADODB.Recordset
rs.Open sSQLRN00900900, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Check values in first table
If rs.RecordCount <> 0 Then
   Do Until rs.EOF
      sField1 = rs("Query2.SOBP")
      sField2 = rs("Query1.SOBP")
         If sField1 = sField2 Then
now here i want to find the value of field which is one of these(RN00736736,RN00713713,RN00900900,RN00144GM3, RN00900900,RN00144GM3)
and put it in the right table which i have opened before.so in the following statement the rsX should be one of the tables above matching the field.

            rsX.AddNew
            rsX("Field1") = rs("Field1")
            rsX("Field2") = rs("Field2")
            'etc through fields
            rsX.Update
         Else
            rsY.AddNew
            rsY("Field1") = rs("Field1")
            rsY("Field2") = rs("Field2")
            'etc through fields
            rsY.Update
         End If
   rs.MoveNext
   Loop
End If

rsX.Close
rsY.Close
rs.Close

End Sub

mmcdonal · February 5th, 2007, 10:25 AM

Make sure query3 has the Appended field added, and the criteria set to No.

Try to figure this out yourself. You have most of it already.

Then do this instead:

sField1 = rs("Field1")

Select Case sField1
   Case "RN00736736"
     rsRN00736736.AddNew
     ...
     rsRN00736736.Update
   Case "RN00713713"
     rsRN00713713.AddNew
     ...
     rsRN00713713.Update
   Case ...

End Select

rs.MoveNext
Loop
etc

Does that help?

mmcdonal

voskoue · February 6th, 2007, 04:02 AM

This is what i did.. can u have a look at it please.. am so sorry for bugging u again.

if the two fields are equal.. is doing that

if not equal what should i do?

i am a begginner and i was given this thing that is above my knowledge... good resource for reading? websites?

Public Sub Find_Couyntries()

Dim rs, rsRN00144GE5, rsRN00144GM3, rsRN00713713, rsRN00736736, rsRN00900900 As ADODB.Recordset
Dim sSQL, sSQLA, sSQLRN00144GE5, sSQLRN00144GM3, sSQLRN00713713, sSQLRN00736736, sSQLRN00900900 As String
Dim sField1, sField2 As String

'Open Recordset on the table you are checking:
sSQL = "SELECT * FROM query3"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

sSQLA = "SELECT * FROM query3 WHERE [Query2.Appended] = 0"

'Open table RN00144GE5 for Appending
sSQLRN00144GE5 = "SELECT * FROM RN00144GE5"
Set rsRN00144GE5 = New ADODB.Recordset
rs.Open sSQLRN00144GE5, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00144GM3 for Appending
sSQLRN00144GM3 = "SELECT * FROM RN00144GM3"
Set rsRN00144GM3 = New ADODB.Recordset
rs.Open sSQLRN00144GM3, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00713713 for Appending
sSQLRN00713713 = "SELECT * FROM RN00713713"
Set rsRN00713713 = New ADODB.Recordset
rs.Open sSQLRN00713713, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00736736 for Appending
sSQLRN00736736 = "SELECT * FROM RN00736736"
Set rsRN00736736 = New ADODB.Recordset
rs.Open sSQLRN00736736, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00736736 for Appending
sSQLRN00736736 = "SELECT * FROM RN00736736"
Set rsRN00736736 = New ADODB.Recordset
rs.Open sSQLRN00736736, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open table RN00900900 for Appending
sSQLRN00900900 = "SELECT * FROM RN00900900"
Set rsRN00900900 = New ADODB.Recordset
rs.Open sSQLRN00900900, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Check values in first table
If rs.RecordCount <> 0 Then
   Do Until rs.EOF
      sField1 = rs("Query2.SOBP")
      sField2 = rs("Query1.SOBP")

        sField1 = sField2

         Select Case sField1
            Case "RN00736736"
             rsRN00736736.AddNew
             rsRN00736736("Query2.SOBP") = rs("Credit")
             rsRN00736736("Query1.SOBP") = rs("Debit")
             rsRN00736736.Update

             Case "RN00144GE5"
             rsRN00144GE5.AddNew
             rsRN00144GE5("Query2.SOBP") = rs("Credit")
             rsRN00144GE5("Query1.SOBP") = rs("Debit")
             rsRN00144GE5.Update

             Case "RN00144GM3"
             rsRN00144GM3.AddNew
             rsRN00144GM3("Query2.SOBP") = rs("Credit")
             rsRN00144GM3("Query1.SOBP") = rs("Debit")
             rsRN00144GM3.Update

             Case "RN00713713"
             rsRN00713713.AddNew
             rsRN00713713("Query2.SOBP") = rs("Credit")
             rsRN00713713("Query1.SOBP") = rs("Debit")
             rsRN00713713.Update

              Case "RN00900900"
             rsRN00900900.AddNew
             rsRN00900900("Query2.SOBP") = rs("Credit")
             rsRN00900900("Query1.SOBP") = rs("Debit")
             rsRN00900900.Update

           Case Else
          Select Case sField2
           Case "RN00736736"
             rsRN00736736.AddNew
             rsRN00736736("Query2.SOBP") = rs("Credit")
             rsRN00736736("Query1.SOBP") = rs("Debit")
             rsRN00736736.Update

             Case "RN00144GE5"
             rsRN00144GE5.AddNew
             rsRN00144GE5("Query2.SOBP") = rs("Credit")
             rsRN00144GE5("Query1.SOBP") = rs("Debit")
             rsRN00144GE5.Update

             Case "RN00144GM3"
             rsRN00144GM3.AddNew
             rsRN00144GM3("Query2.SOBP") = rs("Credit")
             rsRN00144GM3("Query1.SOBP") = rs("Debit")
             rsRN00144GM3.Update

             Case "RN00713713"
             rsRN00713713.AddNew
             rsRN00713713("Query2.SOBP") = rs("Credit")
             rsRN00713713("Query1.SOBP") = rs("Debit")
             rsRN00713713.Update

              Case "RN00900900"
             rsRN00900900.AddNew
             rsRN00900900("Query2.SOBP") = rs("Credit")
             rsRN00900900("Query1.SOBP") = rs("Debit")
             rsRN00900900.Update

           End Select

   rs.MoveNext
     Loop
End Select

rsRN00144GE5.Close
rsRN00144GM3.Close
rsRN00713713.Close
rsRN00736736.Close
rsRN00900900.Close
rs.Close

End Sub