passing date criteria to query from control on form

[brother]

Hi guys,

I want to make a date criteria for my query. The query should return records from the same year as the date field on my form. This is probably a easy one for you guys.

The date field is short date in the table, with an input mask 00.00.0000;0;_.

I've tried the following in the query's criteria field:

Year([Forms]![FrmProject]![Date])

Anyone out there that can help me on my way? thanks!!

 

[HAMMAMABUARQOUB]

The query should return records from the same year as the date field on my form

You sould use the same Year() Function for the query field not only in the criteria, coz eles you will be requesting years to match full dates...
So: in the Where Condition it should be:

Where Year([MYQryfield] = Year([MyFormField])

regards...

 

[brother]

yeah! that did the trick

Thanks you!!

 

[HAMMAMABUARQOUB]

you are wellcme! glad to help

 

[Rich]

You should not have a field or control named Date, it's a reserved word in Access

 

[brother]

My example was just a simplified illustration of the problem. But thanks for the info!

 

[Rich]

There are lists of them somewhere,but Name is another one to lookout for

 

[james5478]

Hi all,

I hope you guys are doing great. I have a form I am working on and need some help.
To give you an idea on the structure, the form is based on a query, which in turn is based on a table.

On the form, I have a StartDate and EndDate textbox (formatted Short Date) where users can enter the dates. I also have a command button that runs the query when the click it. I need the dates they enter to pass to the query.
I have some other conditions that I would like to integrate into this as well.
· If the user only enters a StartDate, give all records from that StartDate.
· If the user only entered an EndDate, give all records up to that EndDate.
· If the user leaves both fields blank, pull all records.

Below is my code in VBA . I could send you a couple images of the form and query if it would help for context too.
Thanks and have a great day!
James5478
Option Compare Database
Option Explicit
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim stDocName As String
Dim stDateField As String
Dim stWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
stDocName = "qryStat"
stDateField = "[Dte]"
lngView = acViewPreview

If IsDate(Me.txtStartDate) Then
stWhere = "(" & stDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If stWhere <> vbNullString Then
stWhere = stWhere & " AND "
End If
stWhere = stWhere & "(" & stDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

Debug.Print stWhere
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub