run query based a date range pulled from a form

[jayhud33]

I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.

my code I am working on is:

Dim startdate2 As String
Dim enddate2 As String
Me.start_date = startdate2
Me.end_date = enddate2
Dim SQL As String

SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'

DoCmd.RunSQL SQL

I am getting a error on the sql = statement, I can't seem to get the statement right.

 

[FoFa]

First, I think you need it this way
startdate2 = Me.start_date
enddate2 = Me.end_date
To assign the value in your form to your variables.

This needs to be like this:
SQL = "SELECT FROM project WHERE [Date] between #" & startdate2 & "# and #" & enddate2 & "#" because Access date literals need to be enclosed in #'s.

Also the column named Date is very bad as Date is a reserved word. I would change that column name to something else.

 

[jayhud33]

Thank you, i will make the changes and see how it goes.

 

[jayhud33]

made changes to table and modified the assigning

still getting error

Dim startdate2 As String
Dim enddate2 As String
startdate2 = Me.start_date
enddate2 = Me.end_date


Dim SQL As String

SQL = "SELECT FROM frmproject WHERE [RepairDate] between #" & startdate2 & "# and #" & enddate2 & "#"

DoCmd.RunSQL SQL

getting run-time error '3141'
The SELECT statement includes a reserved word or an argument name that is misspelled or misssing, or the punctuation is incorrect.

I figured that since I changed Date to RepairDate it would clear this up.

 

[jayhud33]

oops

that sould be project and not frmproject