run-time error - 3075

  • Thread starter Thread starter Elsie
  • Start date Start date
E

Elsie

hi all, the following code gave error 3075 when Last_MDY is 11/31/2003 and
Next_MDY is 1/1/2004. Why is this so?
I am looking at all invoice between 31/11/2003 and 1/1/2004 - which means
December invoices.

(((PUB_customer.[cust-type])=" & Chr(34) & "PRJT" & Chr(34) & ") AND
(([PUB_inv-hdr].[inv-date])>#" & _
Last_MDY & "# And ([PUB_inv-hdr].[inv-date])<#" & Next_MDY & "#) AND " &
_
"((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD',#" & _
"9/1/2003#,#" & Current_MDY & "#)))
 
hi all, more info on the error message:

Run-time error '3075':
Syntax error in date in query expression
'(((PUB_customer.[cust-type]="PRJT") AND
(([PUB_inv-hdr].[inv-date[)>#11/31/2003# And
([PUB_inv-hdr].[inv-date])<#1/1/2004# AND
((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD,#91

this happens only when i select for decemeber 2003, october and november
2003 works fine.
 
I think November in normal calendar has only 30 days ...

--
HTH
Van T. Dinh
MVP (Access)




Elsie said:
hi all, more info on the error message:

Run-time error '3075':
Syntax error in date in query expression
'(((PUB_customer.[cust-type]="PRJT") AND
(([PUB_inv-hdr].[inv-date[)>#11/31/2003# And
([PUB_inv-hdr].[inv-date])<#1/1/2004# AND
((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD,#91

this happens only when i select for decemeber 2003, october and november
2003 works fine.


Elsie said:
hi all, the following code gave error 3075 when Last_MDY is 11/31/2003 and
Next_MDY is 1/1/2004. Why is this so?
I am looking at all invoice between 31/11/2003 and 1/1/2004 - which means
December invoices.

(((PUB_customer.[cust-type])=" & Chr(34) & "PRJT" & Chr(34) & ") AND
(([PUB_inv-hdr].[inv-date])>#" & _
Last_MDY & "# And ([PUB_inv-hdr].[inv-date])<#" & Next_MDY & "#) AND
Click to expand...
"
&
_
"((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD',#" & _
"9/1/2003#,#" & Current_MDY & "#)))
Click to expand...
Click to expand...
 
oh ya... what a stupid mistake... thanks...

how can I check for this kind of mistake? cos I have a form for user to
select the starting month/day/year and ending month/day/year and current
month/year....



Van T. Dinh said:
I think November in normal calendar has only 30 days ...

--
HTH
Van T. Dinh
MVP (Access)




Elsie said:
hi all, more info on the error message:

Run-time error '3075':
Syntax error in date in query expression
'(((PUB_customer.[cust-type]="PRJT") AND
(([PUB_inv-hdr].[inv-date[)>#11/31/2003# And
([PUB_inv-hdr].[inv-date])<#1/1/2004# AND
((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD,#91

this happens only when i select for decemeber 2003, october and november
2003 works fine.


Elsie said:
hi all, the following code gave error 3075 when Last_MDY is 11/31/2003 and
Next_MDY is 1/1/2004. Why is this so?
I am looking at all invoice between 31/11/2003 and 1/1/2004 - which means
December invoices.

(((PUB_customer.[cust-type])=" & Chr(34) & "PRJT" & Chr(34) & ") AND
(([PUB_inv-hdr].[inv-date])>#" & _
Last_MDY & "# And ([PUB_inv-hdr].[inv-date])<#" & Next_MDY & "#)
Click to expand...
Click to expand...
AND
"
&
_
"((PUB_currate.[eff-date])=IIf([PUB_custaddr].[curr-code]='SGD',#"
Click to expand...
Click to expand...
Click to expand...
&
_
Click to expand...
Click to expand...
 
oh ya... what a stupid mistake... thanks...

how can I check for this kind of mistake? cos I have a form for user to
select the starting month/day/year and ending month/day/year and current
month/year....
Click to expand...

If you're always looking for invoices within a single calendar month,
consider using a criterion such as
= DateSerial(NZ([Enter year (leave blank for this year):], Year(Date()), NZ([Enter month number (leave blank for this month):], Month(Date()), 1) AND < (NZ([Enter year (leave blank for this year):], Year(Date()), NZ([Enter month number (leave blank for this month):], Month(Date()) + 1, 1)
Click to expand...

This will correctly span all the date/time values within a calendar
month, and default to this month (to date) if the user leaves both
prompts blank.
 
could you explain the meaning of this?
= DateSerial(NZ([Enter year (leave blank for this year):], Year(Date()),
Click to expand...
NZ([Enter month number (leave blank for this month):], Month(Date()), 1) AND
< (NZ([Enter year (leave blank for this year):], Year(Date()), NZ([Enter
month number (leave blank for this month):], Month(Date()) + 1, 1)

I don't quite get the (leave blank for this year)... and how do I link this
to my form?

Form Layout:

Current Year/Month: Year Month
End of Last month: Year Month Day
Start of Next month: Year Month Day

I am using >[Forms]![Year_Month_M]![end-month] & "/" &
[Forms]![Year_Month_M]![end-day] & "/" & [Forms]![Year_Month_M]![end-year]
And <[Forms]![Year_Month_M]![start-month] & "/" &
[Forms]![Year_Month_M]![start-day] & "/" &
[Forms]![Year_Month_M]![start-year] as criteria in my query.








John Vinson said:
oh ya... what a stupid mistake... thanks...

how can I check for this kind of mistake? cos I have a form for user to
select the starting month/day/year and ending month/day/year and current
month/year....
Click to expand...

If you're always looking for invoices within a single calendar month,
consider using a criterion such as
= DateSerial(NZ([Enter year (leave blank for this year):], Year(Date()),
Click to expand...
Click to expand...
NZ([Enter month number (leave blank for this month):], Month(Date()), 1) AND
< (NZ([Enter year (leave blank for this year):], Year(Date()), NZ([Enter
month number (leave blank for this month):], Month(Date()) + 1, 1)
 
Back
Top