(HELP) Max SQL update query --

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kaseano - 29 Jun 2007 14:56 GMT

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~

Reply to this Message

Douglas J. Steele - 29 Jun 2007 15:11 GMT

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]);"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I can't figure out how to take the Max of two dates in a record.
> My guess was,
[quoted text clipped - 6 lines]
> Any advice would be REALLY appreciated
> Thanks~

Reply to this Message

Kaseano - 29 Jun 2007 15:50 GMT

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)

> 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
[quoted text clipped - 23 lines]
> > Any advice would be REALLY appreciated
> > Thanks~

Reply to this Message

Douglas J. Steele - 29 Jun 2007 20:24 GMT

As I said, it's far, far better to put your calculations in a query, and use
the query wherever you would otherwise have used the table.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks Doug that worked.
>
[quoted text clipped - 33 lines]
>> > Any advice would be REALLY appreciated
>> > Thanks~

Reply to this Message