Access vba

When you want to invoke that query by DoCmd.OpenQuery, the easiest way is to set a TempVar first. Then call OpenQuery and use TempVars() in your queries condition.

Select fld1, fld2, fld3, Date() as MyDate, fld4, fld5 From yourtable;

Hi Ste5an,

Can you please explain with an example it would be great as i am new in using vba. I have a variable which contains date time at which a file has been created. I need to create a query which hold this value

Thx

Thanks

E.g.

Option Compare Database
Option Explicit

Private Sub btnOpenQuery_Click()

  Dim DateTime As Date
  
  DateTime = #9/8/2018#
  TempVars.Add "DateTime", DateTime
  DoCmd.OpenQuery "Query1"

End Sub

Select allOpen in new window

With the query as
EE29116905.accdb

You can adjust the SQL of a query:

MyDate = <some date> ' your variable

Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("YourQuery")
' Define your SQL
qd.SQL = "Select * From YourTable Where DateField = #" & Format(MyDate, "yyyy\/mm\/dd") & "#"

Select allOpen in new window

Why would you go through the extra steps of defining and populating a TempVar when you can simply use the Date() or Now() function depending on whether you want just the date or both the date and the time?

@Pat: Where do you read Now()? I don't see that in the question.

You can also do this by displaying the date you are trying to query against in a form, then refer to that form control in your query:

SELECT * FROM yourTable WHERE [TransDate] >= [Forms]![yourFormNameHere]![txtStartDate]

Although I generally use the tempvars method mentioned above.

Dale

ste5an,  I only included Now() because you named the variable DateTime.  Date() includes only date.  Now() includes date and time.

I simply don' see an advantage to creating a tempvar to save something that is available directly as a function.  If I wanted to use a variable, I would do what Dale suggested and reference a form field.  I'm not saying that it is wrong to use a TempVar, just that it is an extra step that I wouldn't take.

When I do create TempVars, I create them ALL in the same common module no matter where they are used.  That way I don't have to search the entire code base to see all the TempVars I have created for the app.

Ah, I see. Sorry for that. "DateTime" was just a "random" picked variable name ;)

TempVars is the choice when using OpenQuery without further constraint. Indeed using a reference to an open form is also a possibility.