DateAdd or DLookup Problem

G

Gilrucht

Registered User.
Local time
Today, 01:33
Joined
Jun 5, 2005
Messages
132
I am using a DLooking Statement to pull a persons birthdate from another table and populate a unboundbound text box in my form bound to different table. This statement appears to work and pulls in the create date. I say appears because it put a # on either side of the date it pulls in-ie #06-June-91#
Here is the code:

--------
DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & _
"#"
-------

I then use the dateadd function to determine the value of a boundfield named "statoflimits". When I fire the event by putting adate in my accidentdate field I get an errorcode 13 typemismatch error on the first line of my subroutine. Here is the subroutine.
Private Sub AccidentDate_AfterUpdate()
If DateAdd("yyyy", 18, Me.Txt14) > Me.AccidentDate Then
Me.StatofLimits = DateAdd("yyyy", 20, Me.Txt14)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.AccidentDate)
End If

End Sub

Can anyone help?
 
Hi there, guess who!

It seems to me I solved this particular problem by Dim VariableName As Date. Then I would VariableName = Dlookup(...
Then TextBox = VariableName

I do not believe TextBoxes can be anything but text but I could be wrong. I did all of my > and < against the VariableName and not the TextBox.
 
Hi there,
You are going to send me a bill. LOL I didn't know that about textboxes. What was driving me crazy was the same code worked when I pulled the date into the textbox from a combobox. If you haven't realized it the dlookup statement is an arguement in one of the case statements. If you remember my original post on the issue I was trying to pull in a date from another table then we got off on rewriting the whole statement. Now I have the case statements working with your new code and am back to my original problem. Your lookup arguement in the case statement is pulling in the date to the textbox but putting # on either side of the date it pulls in.

Now that you realize it is part of the case statement how would I add the dim statement? I am already using the dim string sttement.
 
You are right. I typed a date in the textbox and the calculation ran perfectly. I have been struggling with trying to add the correct variable statement but keep getting type13 mismatch errors. Here is is my latest try:

Private Sub Combo62_AfterUpdate()
Select Case Me.Combo62
Dim strFrm As String
Dim dob As Date
Case "Auto Accidents"
strFrm = "frm_AutoAccidents"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else


'save pending edits
DoCmd.RunCommand acCmdSaveRecord
strParameters = Me.SCVClientID & ";"
strParameters = strParameters & Me.SCVFileNo & ";"
strParameters = strParameters & Me.txt36 & ";"
strParameters = strParameters & Me.txt38 & ";"
strParameters = strParameters & Me.txt40 & ";"
strParameters = strParameters & Me.txt42 & ";"
dob = DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & _
"#"
--------------------

Can you or someone else tell me what the correct statement should be for my variable? Thanks.
 
Hi Again,

The Date Variable belongs in the receiving Form like:
Option Compare Database
Option Explicit
Dim MyDate As Date '-- Up here makes it public to the module and any code can see and use it.

-------------------------------------------
And here's the receiving form's Load event:
--------------------------------------------
Private Sub Form_Load()

Dim Args As Variant

MyDate = Date() ' Default to today's date

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.SCRClientID = Args(0)
Me.SCRFileNo = Args(1)
Me.txt35 = Args(2)
MyDate = Args(3) ' Assuming this is the passed date
'--Copy the date to a text box
Me.TxtDate = MyDate
'-- Or maybe something like:
Me.txt35 = DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Args(2))
'-- If you just pass the ClientID and let this form do the DLookup (maybe)
End If

End Sub

And then putting the string of Parameters together needs a little fixin'


strParameters = strParameters & Me.txt42 & ";#" & _
DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & "#"

The date needs to be surrounded by #TheDate# so if you displayed the strParameters would end with ;#01/22/1944# which will go into the receiving date field just fine.

Post back if you need additional clarification.
 
Last edited:
Closer but still not there. Created the module and posted the code you gave me. The accident field is now reading the date in the textbox field but is not performing the calculation properly. Heres the code::


-----------
Private Sub Form_Load()
Dim Args As Variant
MyDate = Date() ' Default to today's date

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.AAClientID = Args(0)
Me.AAFileNo = Args(1)
Me.txt36 = Args(2)
Me.txt38 = Args(3)
Me.txt40 = Args(4)
Me.txt42 = Args(5)
Me.txt14 = Args(6)
MyDate = Args(7) ' Assuming this is the passed date
'--Copy the date to a text box
Me.TxtDate = MyDate
End If

End Sub
-----------

Heres the calculation::
------
Private Sub AccidentDate_AfterUpdate()
If DateAdd("yyyy", 18, MyDate) > Me.AccidentDate Then
Me.StatofLimits = DateAdd("yyyy", 20, MyDate)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.AccidentDate)
End If

End Sub

If I putt a testdate in the textbox of 1991 statoflimits should return a date of 2011 but it returns a date of 1993. Its skipping right to the else statement. I know the calculation is right. It worked perfectly when I pulled the date into the text box from a combobox. That is what I really don't understand. I know you say I can't pass a date to a textbox but if that is true why did it work when I passed to date to the same textbox from a combobox? I know I have to be close but I don't know what the problem is. Is it possible to get rid of the # signs around the date and try and pass it as a date? or can you spot a mistake in my code?
 
DLookup("Birthdate", "tbl_clientinfo", "(ClientID = " & Me.SCVClientID &")")
 
It looks like I will have to eat a few of my words. I just found some of my code where I have dates in textboxes and blindly adding and comparing at will. It seems to be working just fine. Don't know why I had to go down that other path.

You still need the #...# in the passed date so Access will treat it as a date when you put it in the TextBox. What is the name of the TextBox you were using originally? Maybe Me.txtBirthday?

Let's put this diagnostic code in the receiving Form_Load event after the Split statement:

MsgBox "Incoming Birthday is [" & Args(7) & "]"

Don't kill me for it but I'm pretty sure you can go back to using the TextBox you have on the receiving form. Sorry. No reason to change back yet until we determine why the comparing code is functioning the way it is.
 
Rural,
No problem. You've given me more time than I have a right to expect and I appreciate it. The answer to your question is the txtbox has alwsways been txt14. Maybe it will help if you know the purpose of the calculation. There is another thread where I sought help to get this calculation. I need to determine the Statutue of Limitations for the Auto Cases. If the person is a mior the statute is 2 years from his 18th birthday. If he is an adult it is 2 years from the accident date. So what I am doing is pulling the date of birth from my main table into the textbox(txy14) the calculation is in the after update of the accident event of the accident field. It adds 18 years to the date in the textbox then compoares the accident date with the textbox date to determine which is greater. If the text box is greater the person is a minor and the statute of limitations date is the textbox date(date of birth) plus 20 years. If the accident date is greater than than textdate plus 18 then person is adult and staute of limitations is accident date plus 2 years. When I was originally pulling clientid, fileno and birthdate into the form via a combobox the calculation worked perfectly. It is only since I got rid of the combobox and went to the dlookup that I have had problems with the calculations. It makes no sense to have a combobox just for the date . I'll try your diagnostic code and let you know.
 
It looks to me as if the receiving form *always* needs the Birthdate and the Birthdate is always located in tbl_clientinfo and it appears that you *always* pass the ClientID or the form will not work. That is just a guess, I could be wrong.

If what I said is true I see no reason to pass the BirthDate. Let the receiving form look it up with the passed ClientID

Me.txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = " & Args(0) &")")

Domain Aggregate functions take time and this will pass some of that delay time on to the receiving form. If you don't always need the BirthDate and you don't *always* pass the ClientID or the BirthDate is not *always* in tbl_clientinfo then this will not work.

Two thoughts to add on...

Me.txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = '" & Args(0) &"'")")

Post the Form_Load code you have now so I can look at it.
 
Last edited:
Why have you bound a table to the form?
 
Miles,
I haven't as far as I know. All my forms are linked by clientid and caseid. When a new client comes in there are maybe 10 forms to fill out all bound to different tables. What I am doing is passing the clientid and caseid from one form to the next form so the user doesn't have to look them up in a combo as the open each new form. One this one particular form I also need to pull in the clients dob from main clientinfo form on order to perform the statute of limitations calulation. You had helped me come up with the right calculation in another post a while ago when I was using queries and it was working perfectly but I can't seem to get it working since I scrapped the comboboxes and went with openargs and Dlookup. Why do you say I am binding a table to my form?
 
Rural,
Your assumptions are correct. I always need both birthdate and clientid on this form. Here is the curreent code for the case arguement:
-------------
Private Sub Combo60_AfterUpdate()
Select Case Me.Cbo49
Dim strFrm As String




Case "Auto Accidents"
strFrm = "frm_AutoAccidents"

If MsgBox("Please review this form to make sure all data is correct. Are you certain you wish to save this record?", vbQuestion + vbYesNo) = vbNo Then
DoCmd.CancelEvent
Else


'save pending edits
Me.Requery
strParameters = Me.SCVClientID & ";"
strParameters = strParameters & Me.SCVFileNo & ";"
strParameters = strParameters & Me.txt36 & ";"
strParameters = strParameters & Me.txt38 & ";"
strParameters = strParameters & Me.txt40 & ";"
strParameters = strParameters & Me.txt42 & ";"
strParameters = strParameters & "#" & _
DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & _
"#" '


' Then the form open would be:
DoCmd.OpenForm strFrm, , , , , , strParameters
'-- That's it for this case - the other form does everything


End If
'Start Next Case Statement
Case "Bad Faith"
-------------------------

Here is my FormLoad Code:

---------------------
Private Sub Form_Load()
Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.AAClientID = Args(0)
Me.AAFileNo = Args(1)
Me.txt36 = Args(2)
Me.txt38 = Args(3)
Me.txt40 = Args(4)
Me.txt42 = Args(5)
Me.Txt14 = Args(6)

End If

End Sub
--------------


Heres the calculation:
-------------
Heres the calculation::
------
Private Sub AccidentDate_AfterUpdate()
If DateAdd("yyyy", 18, MyDate) > Me.AccidentDate Then
Me.StatofLimits = DateAdd("yyyy", 20, MyDate)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.AccidentDate)
End If

End Sub
----------------

All the arguements are working except for the DLookup Arguement. It looks up the correct date but posts it to the txtbox(txt14) as text and not as a date so the calculation doesn't run when the accident date is plugged in. If I manually type a date into the textbox(txt14) the calculation runs perfectly.

One question, Rural. Where am I putting "Me.txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = '" & Args(0) &"'")") "
 
Last edited:
It looks like you have not put the diagnostic code in yet!

MsgBox "Incoming Birthday is [" & Args(6) & "]"

We really need to see what is getting to the form.

Plus a small problem on this line:

DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & _
"#" '

Remove that last single quote after "#" '<-- this one s/b just "#"
 
Last edited:
Oops, just caught the last question.

One question, Rural. Where am I putting "Me.txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = '" & Args(0) &"'")") "

---------------------
Private Sub Form_Load()
Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.AAClientID = Args(0)
Me.AAFileNo = Args(1)
Me.txt36 = Args(2)
Me.txt38 = Args(3)
Me.txt40 = Args(4)
Me.txt42 = Args(5)
Me.Txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = '" & Args(0) &"'")") "

End If

End Sub
--------------
 
I put in the msg box . got msg daying "Incoming Birthdate is []" and txt was empty14
 
That's what I thought. Definately something wrong with that parameter.

Change the MsgBox to show OpenArgs instead of Args(6). Then we can move the Dlookup into the new form. Have you fixed that extra ' yet?
 
I tried this code and it won't compile. the me.txt14 line is in red and when I compile I get a syntext error

Private Sub Form_Load()
Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
DoCmd.GoToRecord , , acNewRec '-- Calling form needs a new record
Args = Split(Me.OpenArgs, ";")
Me.AAClientID = Args(0)
Me.AAFileNo = Args(1)
Me.txt36 = Args(2)
Me.txt38 = Args(3)
Me.txt40 = Args(4)
Me.txt42 = Args(5)
Me.txt14 = DLookup("Birthdate", "tbl_clientinfo", "(ClientID = '" & Args(0) &"'")")


End If

End Sub
 
Wow, I changed Msg to openarg as you said. Msg was Incoming date = and it listed the values of all the arguements????????
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom