Variable name in DMax

[ohmbru]

The following code is what I'm having trouble with. The value that should be returned is "1/27/2004" but I get "12/29/1899." There is only one record in the table. If I substitute the variable reference with the actual table name I get the correct value. However, I need to be able to access the different tables through the variable.


Public Function RecoverForm()
Dim A As Date
Dim User2 As String

DoCmd.Close acForm, "frmRecover", acSaveNo
DoCmd.OpenForm "frmBeginSession"
DoCmd.Restore
User2 = Forms!frmUser!UserID
DoCmd.OpenTable User2, acViewNormal, acReadOnly
DoCmd.GoToRecord , , acFirst
A = DMax("[" & User2 & "]![Date]", User2, "[" & User2 & "]![Begin]&quot > 0
Forms![frmbeginsession]![Date] = A

Brian

 

[SteveR77]

Brian,

For A since it is a Date/Time value you will need -

#& A &#

HTH,

Steve

 

[RoyVidar]

You are testing the DMax expression against 0. Obviously the DMax provides something > 0, so the expression returns True, and thereby assigns -1 to the datevariable 'A'.

? format(-1,"dd/mm/yyyy&quot -> 12/29/1899

You are using Date as a field name. That will sooner or later create headaches, since it's a function.

I'm not sure what you do here. You are collecting a user id, and put that in variable 'User2'. Then you use this as table name in the DMax function.

Q: Do you have a table for each of your users? Wouldn't it be much easier to have all users in one table (at least for maintenance)?

From the helpfiles on the domain aggregate functions, the first argument (Expr) is the field name (or it might be a calculation...) - no need for a table name.

Next argument (domain) is the table or query name, and the last argument is the criteria (no need for table name). Should your goal here be to fetch the latest date from the table specified in 'User2' where 'begin > 0', then something like this might do (I just had to rename the datefield;-)):

[tt]A = DMax("[MyDate]", User2, "[Begin] > 0&quot[/tt]

- but if it's only one record in the table, do you need a criteria?

Roy-Vidar