DLookup Function with Date (1 Viewer)

[WilsonDC]

This is my first code I've tried to write so forgive basic errors please. I've Searched this forum for examples and issues but all the answers havent worked and I'm not sure if Im even in the ballpark of writing this correctly.

I have a table ,tblVehicles, with vehicle data. When I open my database, (Step One) I want a message box to open if the Registration date in the table has passed today's date. I want to know that a vehicle Registration has expired.

(Step Two) Once i get that, i want to edit code to let me know when it will expire within 30 days .

Im asking for help to get Step One only. I have commented out in the code where i get my error.

Function AutoExec2()

    Dim db As Database
    Dim rs As Recordset
    Dim RegistrationDate As Date

    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblVehicles")
    
    rs!RegistrationDate = "RegistrationDate"
   
     'The Dlookup is where i get an error "Compile Error: Syntax error"
    Dlookup("RegistrationDate", "tblVehicles", "RegistrationDate = #Now()#")
    
     
    If "RegistrationDate" <= Now() Then
        DoCmd.OpenForm "MessageBoxVehReg", acNormal, "", "", , acNormal
    Else
        DoCmd.OpenForm "MessageBoxVehRegUpdated",acNormal,"","", ,acnormal
    
    End If
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    


End Function

 

[Futures_Bright]

Based on what I can see you want the following:

 DLookup("FieldName" , "TableName" , "Criteria= #date#")

see link.

Out of curiosity, why not do this with a query instead?

 

[pr2-eugin]

Hello Wilson, Try this as your DLookUp..

DlookUp("RegistrationDate", "tblVehicles", "RegistrationDate = #" & Date() & "#")

I have replaced the Now with Date as I fell RegistationDate field will have only the date.. As Now() would provide Date and Time.. And Date <> Date & Time

? Date()
11/03/2013 

? Now()
11/03/2013 16:54:01 

? Date() = Now()
False

For a complete list of how to use DLookUp check out this link..

Also just curious.. why do you have Recordsets delcared? and not used anywhere? Also they are quiet not right on its usage..

EDIT : Also, looking at your code again the Syntax error may not be on DLookUp, it might be on the..

rs!RegistrationDate = "RegistrationDate"

You cannot assign values just like that.. It should be first allowed to be edited.. then the appropriate Date value has to be applied and not string.. What type is RegistrationDate?

 

[boblarson]

Also, I didn't see anyone make reference to this, but just using

DLookup(...etc...)

isn't correct. You need to assign the value to something or use an If or Select Case statement.

If DLookup(...etc...)

or

Select Case DLookup(...etc...)

or

MySomething = DLookup(...etc...)

 

[pr2-eugin]

Also, I didn't see anyone make reference to this,

Oops.. I did not see that at all.. But that too..

 

[WilsonDC]

Do I even need to declare a recordset? Do I even need a Dlookup function? Im trying to learn this so if I'm way off, let me know so i don't waste anyone's time. I will keep reading and watching videos to learn more.

 

[Pat Hartman]

DLookup() is a function that runs its own query. You don't need to create a recordset to work with it.

I can't tell by your code what exactly you are trying to do. Perhaps if you tell us in plain English, we can suggest an appropriate solution.

 

[pr2-eugin]

I have a table ,tblVehicles, with vehicle data. When I open my database, (Step One) I want a message box to open if the Registration date in the table has passed today's date. I want to know that a vehicle Registration has expired.

This is quiet vague.. As a table can contain more than one Vehicle that might be past its registration date.. so just using DLookUp will not be ideal.. This is where I think you were planning on using RecordSets, but stuck (am I right ??!!??)..

(Step Two) Once i get that, i want to edit code to let me know when it will expire within 30 days .

You do not have to keep tampering with code over and over again !! This could be sorted by using two simple Queries.

Also, say for example you have 99 vehicles that has past its expiry.. you would not be able to display them neatly using just a message box..

So my advice is; first create the two Queries.. using the Query wizard or by simple copying the code.. (ofcourse changing the names to meet your needs)
This is for expired later than Today,

SELECT allTheRequiredFields, tblVehicles.RegistrationDate 
FROM tblVehicles
WHERE tblVehicles.RegistrationDate < Date();

Save it and give it a name, say Qry_ListOfExipred.. Then create the next Query..

SELECT allTheRequiredFields, tblVehicles.RegistrationDate 
FROM tblVehicles
WHERE tblVehicles.RegistrationDate BETWEEN Date()+1 AND Date()+30;

Save it and give it a name, say Qry_ToBeExpireList..

The next step would be creating a Form that will have two Listboxes.. That will get its data from the two above Queries (use the ListBox wizard, to get this).. Now save it and give it a name, Frm_Reminder..

Then set this Form to be opened up, when you open the DB.. Use File -> Options -> Current Database -> Open Form (select the form from the drop down list)..

Close and reopen.. It should open up with the Form listing all vehicles that expiered and the ones that are about to expire..

 

[WilsonDC]

Yes, pr2-eugin, that is where I am stuck, as you put it. Your example gives me a better picture using the queries as you have explained. Thankyou, my goal is to learn VBA not just get this to work so I appreciate everyones input. Is there a way to do that in a module so I can learn VBA better?

 

[pr2-eugin]

Okay.. Got you.. Well there should be plenty of resources on Recordsets, how to use.. however my favorite one, which I have bookmarked and frequently refer to is UtterAccessWiki, which provides a very basic step by step understanding on how to use them.. OpenRecordset does not only take Table/Query names, they can also take in Raw SQL queries.. So with a little modification to the current code you can achieve what you are trying to do.. however do remember the drawbacks of what I mentioned earlier..

You do not have to keep tampering with code over and over again !!
:
Also, say for example you have 99 vehicles that has past its expiry.. you would not be able to display them neatly using just a message box..

Look over, the link and change/adapt your code.. I would be going offline about now.. So if you have any troubles post back and am sure some one else would help you out.. If not I would be back tomorrow.. Good luck !!
http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners