Thanks Doug that worked.
I'm trying to set up a way to automate a bunch of steps in excel.
1st the excel sheet is imported.
Access then adds all the fields with,
<<<<<>>>>>
Dim db As DAO.Database
'Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set tdf = db.TableDefs("PB")
Set fld = tdf.CreateField("difference", dbSingle)
' fld.Required = True
tdf.Fields.Append fld
Set fld = tdf.CreateField("EffectiveDate", dbDate)
tdf.Fields.Append fld
Set fld = tdf.CreateField("EndDate", dbDate)
tdf.Fields.Append fld
Set fld = tdf.CreateField("MaxDate", dbDate)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Months", dbSingle)
tdf.Fields.Append fld
Set fld = tdf.CreateField("MonthsCap", dbSingle)
tdf.Fields.Append fld
Set fld = tdf.CreateField("backbillamount", dbSingle)
tdf.Fields.Append fld
Set fld = tdf.CreateField("delete", dbByte)
tdf.Fields.Append fld
<<<<<>>>>>>
Then I tried to populate the fields with a bunch of queries via VB
<<<<<<<>>>>>>
strSQL = "SELECT DISTINCT children.CUS_PARENT INTO Parents FROM children;"
DoCmd.RunSQL strSQL
strSQL = "UPDATE Parents INNER JOIN PB ON Parents.CUS_PARENT =
PB.PARENT_ACCT_NO SET PB.[delete] = 1;"
DoCmd.RunSQL strSQL
strSQL = "DELETE PB.*, PB.delete FROM PB WHERE (((PB.delete)=1));"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.[delete] = 1;"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB INNER JOIN backbill ON (PB.USOC_STATE = backbill.ST) AND
(PB.USOC = backbill.USOC) SET PB.EffectiveDate = backbill!EffectiveDate,
PB.[delete] = 0;"
DoCmd.RunSQL strSQL
strSQL = "DELETE PB.*, PB.delete FROM PB WHERE (((PB.delete)=1));"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.difference = PB!Expr1011-PB!CURRENT_PRICE;"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.EndDate = #6/30/2007#;"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.MaxDate = IIf([FEAT_DATE_ADD] >
[EffectiveDate],[FEAT_DATE_ADD], [EffectiveDate]);"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.Months = (PB!EndDate-PB!MaxDate)/30;"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.MonthsCap = IIf(PB!Months>24,24,PB!Months);"
DoCmd.RunSQL strSQL
strSQL = "UPDATE PB SET PB.backbillamount = PB!difference*PB!MonthsCap;"
DoCmd.RunSQL strSQL
db.Close
Set db = Nothing
Set tdf = Nothing
Set fld = Nothing
<<<<<<>>>>>>>>
It's really messy because i'm new.
I chose to run a query off a queried table instead of filtering + deleting
at the same time because it ran a lot faster.
I still need to switch "PB" & the EndDate value to variables.
Then I can hopefully make the importing process easier.
I would love any advice is my strategy for VB is off.
Thanks (doug)
Douglas J. Steele said:
Why would you want to store it, when you can calculate it any time you want?
As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
You'd be far better off simply creating a query with a computed field that
returns the larger value using the IIf function.
If you're determined, though, try:
strSQL = "UPDATE PB " & _
"SET PB.MaxDate = " & _
"IIf([FEAT_DATE_ADD] > [EffectiveDate],[FEAT_DATE_ADD], [EffectiveDate]);"
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Kaseano said:
I can't figure out how to take the Max of two dates in a record.
My guess was,
strSQL = "UPDATE PB SET PB.MaxDate =
Max([PB]![FEAT_DATE_ADD], [PB]![EffectiveDate]);"
(same line)
I was hoping the excel "max" format would work but sadly it dosen't =(
Any advice would be REALLY appreciated
Thanks~
