Syntax Error in Form Filter 2

[Vyurr]

Good morning all,

I can't seem to find the error in this, I'm hoping someone can shed some light. I am trying to filter a form to only show entries from this fiscal year. I have a fiscal year table that I am doing a DLookUp on. When I try to apply the filter, I get the following error:

Syntax error (missing operator) in query expression 'Between DLookUp("[startDate]","tblfiscalyrs","fiscalID=" & Forms!frmStartup!cboFiscal) And DLookUp("[endDate]","tblfiscalyrs","fiscalID=" & Forms!frmStartup!cboFiscal)'

Any ideas what's wrong with this?

Thanks in advance,

V

 

[dhookom]

How are you trying to apply the filter? Can you share some code? Are you comparing the start and end dates to a particular field in your form's record source?

I would think there would be an more efficient solution. Since the fiscal year seems to be coming from a combo box, I would add the StartDate and EndDate as columns in the combo box so the filter to reference these values rather than using DLookup()s.


Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

Hi Duane,

Thanks for the prompt reply. While testing this, I have simply been clicking the apply filter button on the toolbar to attempt to apply the filter. The plan was to add a:

Me.FilterOn = True

later.

You are correct that the filter is looking up values from a combo box on the main startup form. What I'm basically trying to accomplish is I want to filter all of my data entry forms and my reports to only include data from the fiscal year chosen by the user.

The combo box is populated from the following table:

tblFiscalYrs

fiscalID (PK)
fiscalName
startDate
endDate

The combo box is bound to fiscalID and displays only fiscalName.

I hope this is more clear.

Thanks again,

V

 

[Vyurr]

Hello again,

I've got it working now...

Sometimes its the simplest little things.

When I added Between DLookUp("[endDate]","tblfiscalyrs","fiscalID=" & Forms!frmStartup!cboFiscal) And DLookUp("[startDate]","tblfiscalyrs","fiscalID=" & Forms!frmStartup!cboFiscal) to the filter, I forgot to include [Date] at the beginning to let the filter know what field in the form I was referring to.

Thanks again,

V

 

[PHV]

Your combo RowSource:
SELECT fiscalID, fiscalName, startDate, endDate FROM tblFiscalYrs

Your Filter property (set in the AfterUpdate event procedure of cboFiscal):
Me.Filter = "[yourDateField] Between " & Me!cboFiscal.Column(2) & " And " & Me!cboFiscal.Column(3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Vyurr]

Hi PHV,

Thanks for the tip. I had to modify it a little:

Your Filter property (set in the AfterUpdate event procedure of cboFiscal):
Me.Filter = "[yourDateField] Between #" & Me!cboFiscal.Column(2) & "# And #" & Me!cboFiscal.Column(3) & #

...but it works well.

Just as an aside, if I wanted to modify my queries with the same idea, as I understand it, the .Column() function won't work in the Criteria section...any way around that?

V

 

[PHV]

In addition to the ComboBox you may create 2 TextBox named, say, txtStartDate and txtEndDate.
In the AfterUpdate event procedure of the combo:
Me!txtStartDate = Format(Me!cboFiscal.Column(2),"yyyy-mm-dd")
Me!txtEndDate = Format(Me!cboFiscal.Column(3),"yyyy-mm-dd")

In the criteria cell of your date column in the query grid:
Between [Forms]![your formname]![txtStartDate] And [Forms]![your formname]![txtEndDate]

You may have to declare the parameters as DateTime in the query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You don't really need the code if you just set the control sources of the text boxes like:
[tt][blue]
=Format(Me!cboFiscal.Column(2),"yyyy-mm-dd")
=Format(Me!cboFiscal.Column(3),"yyyy-mm-dd")[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, well spotted.
Doesn't should the combo have a valid default value to avoid #Error (or a like) in the textboxes when the form loads ?

 

[dhookom]

PH,
Good question. I don't usually apply the format() function. My control sources would actually be just:
=Me!cboFiscal.Column(2)
=Me!cboFiscal.Column(3)
Being here in the states, I don't have to worry as much about date formats.

Duane
Hook'D on Access
MS Access MVP

 

[Vyurr]

Many thanks to you both.

I think I've learned more in this single thread than I did in the last Access course I took

V