Undefined Function "date expression

[josros60]

Ï am getting the above error undefined function when i run the form or query,

here it's sql view:

SELECT RATE.RATEID, RATE.COMPANY, RATE.COMPANYID, RATE.ENDINGDATE, RATE.SINGLEDOUBLE, RATE.GROUPRATE, RATE.TRIPLE, RATE.QUAD, RATE.SUITE, RATE.COMP, RATE.BFAST, RATE.COMMENTS
FROM RATE
WHERE (((RATE.STARTINGDATE)>=DateValue("1/1/" & Year(Date())) And (RATE.STARTINGDATE)<=DateValue("12/31/" & Year(Date())+1)) AND ((RATE.ENDINGDATE) Is Not Null))
ORDER BY RATE.STARTINGDATE, RATE.ENDINGDATE DESC;

thank you

 

[xenou]

Works for me in access. How are you running the query? Is it in access?

 

[boblarson]

RATE is a built in function so it is an Access Reserved Word and you would need to therefore use square brackets around it in any place you use it.

 

[josros60]

Thanks for replying.

Yes in Access it's the source for subform.

thank you

 

[josros60]

How would I do that in sql view query to fix it, can you show me how?
thank you

 

[xenou]

select [rate].rateid, [rate].company, ...
From [rate] 
where ((([rate].startingdate) ...

 

[josros60]

Thank you.

I put the [] on each one contataining the rate word, but keep gettting same error, I noticed though that when i run the query takes the brackets off again.

thanks you

here what I did:

SELECT[RATE].RATEID, [RATE].COMPANY, [RATE].COMPANYID, [RATE].ENDINGDATE, [RATE].SINGLEDOUBLE, [RATE].GROUPRATE, [RATE].TRIPLE, [RATE].QUAD, [RATE].SUITE, [RATE].COMP, [RATE].BFAST, [RATE].COMMENTS
FROM [RATE]
WHERE ((([RATE].ENDINGDATE) Is Not Null) AND (([RATE].STARTINGDATE)>=DateValue("1/1/" & Year(Date())) And ([RATE].STARTINGDATE)<=DateValue("12/31/" & Year(Date())+1)))
ORDER BY [RATE].STARTINGDATE, [RATE].ENDINGDATE DESC;

 

[xenou]

I think datevalue should be available as a vba function. Be sure you have the reference to it correctly set.

Also make sure you've not gone and created a custom function of your own called datevalue().

1) Alt + F11 to drop into the code editor
2) Tools | References
3) Make sure visual basic for applications is checked

(ignore the fact that I have VBScript Regular Expressions checked in my example - that's not usually needed)

<img alt="refernces" src="http://northernocean.net/etc/mrexcel/20110428_refs.png" />

 

[xenou]

You also could attempt instead a DateSerial based function (though that's a vba function, too, I think):

>=DateSerial(Year(Date()),1,1) 
AND < DateSerial(Year(Date())+1,1,1)

Which would be all dates between Jan 1 and Dec 31 of the current year.

 

[josros60]

the weird thing is that i took the program at home from work, and at home I don't have that issue it opens fine.

I wonder why at work still saying the same thing.

I home same version of Access 2003 bu windows xp, at work is Windows 7 home.

this is driving me crazy why at home works and at work get that error.
thanks for your help