Copying Specific Records From A Field According to another Field By Code

  • Thread starter Thread starter Ange Kappas
  • Start date Start date
A

Ange Kappas

Hi,
I have the below code which copies records from a query to a table
as below which works fine, but I want it in the last line where it copies:

MySet2![DAILY CHARGE] = MySet![Price]

if in the

Myset![PRICELIST] the value is "Z" to copy this instead

MySet2![DAILY CHARGE] = MySet![DAILY CHARGE]

otherwise to continue to copy

MySet2![DAILY CHARGE] = MySet![Price]

What do I have to modify below to make it work.

Thanks
Ange





Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set MySet = db1.OpenRecordset("TODAY CHARGES")
Set MySet2 = db1.OpenRecordset("RESPEL ALL CHARGES")



With MySet
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
MySet2.AddNew
MySet2![Date] = MySet![Date]
MySet2![PELNMB] = MySet![PELNMB]
MySet2![RESNO] = MySet![RESNO]
MySet2![RESNAME] = MySet![RESNAME]
MySet2![COMPANY] = MySet![COMPANY]
MySet2![PRICELIST] = MySet![PRICELIST]
MySet2![HOTELAPT] = MySet![HOTELAPT]
MySet2![ROOMNO] = MySet![ROOMNO]
MySet2![ROOMTYPE] = MySet![ROOMTYPE]
MySet2![BASIS] = MySet![BASIS]
MySet2![ARRIVAL] = MySet![ARRIVAL]
MySet2![DAYS] = MySet![DAYS]
MySet2![DEPARTURE] = MySet![DEPARTURE]
MySet2![SURNAME] = MySet![SURNAME]
MySet2![NAME] = MySet![NAME]
MySet2![DAILY CHARGE] = MySet![Price]
MySet2.Update
..MoveNext
Loop
End With
MySet2.Close
 
Hi Ange,

You were really close to answering your own question - a function you can
use to implement your solution is the IIF(expression to evaluate, return if
true, return if false)

Try this -

MySet2![DAILY CHARGE]=IIF(Myset![PRICELIST]="Z",MySet![DAILY CHARGE],
MySet![Price])

Hope this helps...
Gordon
 
Thanks Gordon, It worked, you can't imagine how long I've been trying to
work this problem out using SQL and queries, and it was solved via code,

Thanks Again !!!

Ange



gllincoln said:
Hi Ange,

You were really close to answering your own question - a function you can
use to implement your solution is the IIF(expression to evaluate, return
if true, return if false)

Try this -

MySet2![DAILY CHARGE]=IIF(Myset![PRICELIST]="Z",MySet![DAILY CHARGE],
MySet![Price])

Hope this helps...
Gordon

Ange Kappas said:
Hi,
I have the below code which copies records from a query to a table
as below which works fine, but I want it in the last line where it
copies:

MySet2![DAILY CHARGE] = MySet![Price]

if in the

Myset![PRICELIST] the value is "Z" to copy this instead

MySet2![DAILY CHARGE] = MySet![DAILY CHARGE]

otherwise to continue to copy

MySet2![DAILY CHARGE] = MySet![Price]

What do I have to modify below to make it work.

Thanks
Ange





Dim PoseidonHotelProgram As Database
Dim rsta As DAO.Recordset
Dim frm As Form
Dim Msg, Style, Title, Response, MyString

Set db1 = CurrentDb()
Set MySet = db1.OpenRecordset("TODAY CHARGES")
Set MySet2 = db1.OpenRecordset("RESPEL ALL CHARGES")



With MySet
If Not .EOF Then
.MoveFirst
End If
Do While Not .EOF
MySet2.AddNew
MySet2![Date] = MySet![Date]
MySet2![PELNMB] = MySet![PELNMB]
MySet2![RESNO] = MySet![RESNO]
MySet2![RESNAME] = MySet![RESNAME]
MySet2![COMPANY] = MySet![COMPANY]
MySet2![PRICELIST] = MySet![PRICELIST]
MySet2![HOTELAPT] = MySet![HOTELAPT]
MySet2![ROOMNO] = MySet![ROOMNO]
MySet2![ROOMTYPE] = MySet![ROOMTYPE]
MySet2![BASIS] = MySet![BASIS]
MySet2![ARRIVAL] = MySet![ARRIVAL]
MySet2![DAYS] = MySet![DAYS]
MySet2![DEPARTURE] = MySet![DEPARTURE]
MySet2![SURNAME] = MySet![SURNAME]
MySet2![NAME] = MySet![NAME]
MySet2![DAILY CHARGE] = MySet![Price]
MySet2.Update
.MoveNext
Loop
End With
MySet2.Close
Click to expand...
Click to expand...
 
Back
Top