1

I keep getting a 3075 missing operator message on this line of code: DoCmd.OpenForm ““frmBilling””, acNormal, , “”[ParentID] = “” & “” “” & Me.[txtName] & “” “” & “”[DateAttended] Between #“” & Me.[TxtStartDate] & “”# and #“” & Me.[txtEndDate] & “”#“” I cannot work out what is wrong with it can anyone help please. If I just use: DoCmd.OpenForm ““frmBilling””, acNormal, , “”[ParentID] = “” & “” “” & Me.[txtName] it works fine. If i use: DoCmd.OpenForm ““frmBilling””, acNormal, , “”[DateAttended] Between #“” & Me.[TxtStartDate] & “”# and #“” & Me.[txtEndDate] & “”#“”, It works, but put the two together and the error message appears. Thanks in advance

2

Glad you got it sorted but one question:

In your original post you said that
DoCmd.OpenForm “frmBilling”, acNormal, , “[DateAttended] Between
#” & Me.[TxtStartDate] & “# and #” & Me.[txtEndDate] & “#”,

works, but i guess that wasn’t quite true if you still needed to format
the dates.

Martin

3

I’ll remember that, I suppose that’s what we get from living in the better half of the world.
Thanks again.

Regards
Ian

4

Dates are often a problem. Being in NZ, I always as a matter of course use the formatting I mentioned for dates. Access manages dates in American format and doesn’t translate the way we display dates all that well. Once I realized you were Australian that indicated you would probably be having the same date issues we have.

Cheers
Mary

5

Your bloods worth bottling Mary doing the formatting worked, thanks to all who helped, Access is such quirky program.

Ian

6

Thanks Martin Mary’s suggestion on formatting the dates worked.

Ian

7

That suggests that txtname is not a string so use the first one. You may need to format the dates to American format eg instead of just have me.txtstartdate you will want format(me.txtstartdate,“mm/dd/yyyy”)

Mary

8

Hi Ian,

What Mary’s suggesting is right if you want both criteria to be
combined. Do you want that? Or (no pun intended) do you want the
criteria to be combined, in which case you need to use the OR
keyword.

DoCmd.OpenForm “frmBilling”, acNormal, , “ParentID = " & Me.txtName & "
OR (DateAttended Between #” & Me.TxtStartDate & “# and #” &
Me.txtEndDate & “#)”

Either way, you need to have a key word between any criteria that you
specify. And brackets (as shown) may also be useful.

HTH
Martin

9

Thank you Mary for your quick response unfortunately neither of the suggestions work, the first only filters by the ParentID and does not include the date filter,
The second suggestion I get a type mismatch error.

Ian

10

You need an “AND” between your 2 criteria

DoCmd.OpenForm “frmBilling”, acNormal, , “ParentID = " & Me.txtName & " AND DateAttended Between #” & Me.TxtStartDate & “# and #” & Me.txtEndDate & “#”
I am assuming from your code that me.txtname is not returning a string
if it is you will need some single quote marks

DoCmd.OpenForm “frmBilling”, acNormal, , “ParentID = '” & Me.txtName & “’ AND DateAttended Between #” & Me.TxtStartDate & “# and #” & Me.txtEndDate & “#”

Mary

11

It’s easier to debug and maintain if you put the full sql statement in a variable and then use that variable.

Do that, and post the sql variable contents back here after it errors.

***pardon my spelling, this was sent from a mobile