Error in query expression Date()

[boywonder]

I'm getting the following error:



Function is not available in expressions in query expression 'DateDiff("d",[cone fees],Date())'.

This is coming from a query with only one table setup in it. After I get this error, it highlights the Date(). So I changed the Date() to Now() and then I get the error again and it highlights DateDiff.

Could someone tell me why this is happening? I'm using XP as the OS with sp3.

[jzwp11]

Can you provide the entire SQL text of the query? Also what is the data type of the [cone fees] field?

[boywonder]

It work great on all other XP system. But for some reason, now every pc i copy and paste it to, I get the above problem. It also happens with Windows 7 Pro w/ XP mode edition. A lot of the forum I have been reading about state it might have something to do with references. Following is the SQL statement:

SELECT [OLD TRANS].fees, [OLD TRANS].[cone check], [OLD TRANS].[cone date], [OLD TRANS].[cone fee], DateDiff("d",[cone fee],Date()) AS DAYS, [OLD TRANS].[cone fee return date], [OLD TRANS].total, [OLD TRANS].discount, [OLD TRANS].deposit, [OLD TRANS].[check number], [OLD TRANS].returned, [OLD TRANS].[returned date], [OLD TRANS].[return status], [OLD TRANS].bank, [OLD TRANS].date, Int(((([check amount]+10)/10)+([check amount]+10))/10)+10 AS [cone2 fee], IIf([CHECK TYPE] In ("P","G"),IIf(CCur([check amount]*[Forms]![register]![STORE INFO].[Form]![IN STATE FEES])<1,1,CCur([check amount]*[Forms]![register]![STORE INFO].[Form]![IN STATE FEES])),IIf(CCur([check amount]*[Forms]![register]![STORE INFO].[Form]![OUT STATE FEES])<1,1,CCur([check amount]*[Forms]![register]![STORE INFO].[Form]![OUT STATE FEES]))) AS [check fee], [OLD TRANS].[REGISTER TOTAL], [OLD TRANS].clerk, [OLD TRANS].[REF DISCOUNT], [OLD TRANS].nsf, [OLD TRANS].BALANCE, [OLD TRANS].STATUS, [OLD TRANS].[payment type], [OLD TRANS].[payment number], CCur(IIf([CHECK TYPE] In ("P","G","O"),[TOTAL],IIf([CHECK TYPE]="H",[CHECK AMOUNT],0))) AS [TO], CCur(IIf([CHECK TYPE] In ("B","N"),[TOTAL],0)) AS [FROM], [OLD TRANS].promo, [OLD TRANS].[from fees], [OLD TRANS].[store no], [OLD TRANS].[real fees], [OLD TRANS].[Register Reading], IIf([check amount]=329,Int([check amount]*(21.75/100)),Int([check amount]*([Forms]![register]![STORE INFO].[Form]![STATE PRECENTAGE]/100))) AS [Memphis Cone2 fees], IIf([check amount]>320,Int([check amount]*(21.75/100)),[check amount]*([Forms]![register]![STORE INFO].[Form]![STATE PRECENTAGE]/100)) AS [Miss cone2 fees], [OLD TRANS].[Bank Return Date], [OLD TRANS].[Promise Date], [OLD TRANS].DAYS, [OLD TRANS].Lineno
FROM [OLD TRANS]
ORDER BY [OLD TRANS].date DESC;

[jzwp11]

It could be a missing reference. One way to check is to go to the VBA window and check for any missing references (tools-->references). If there are any missing references you will see the word MISSING in front of the references.

BTW, what is the data type of the [cone fees] field? The name does not suggest a date. The datediff() function requires 2 date fields or expressions that result in dates.

[weekend00]

do you actually want ateDiff("d",Date(),[cone fees]) ?

[boywonder]

It's a Date/Time format. I'm using Access 2003. I don't see reference under the Tools menu.

[boywonder]

I will try it your way weekend00. Let you know whether or not it worked.

[boywonder]

It didn't work weekend00. Thanks anyway. Windows 7 just give a message saying 'MS Office Access has stop working'. Other forms per query works OK. It just this one form with this query in it just seem to do not want to work.

[weekend00]

I think on the countraty, Access is working when Windows 7 says 'MS Office Access has stop working' because it may take Access some time to finish the job.

At least, the syntax is correct. if it takes much longer than you expected, maybe because you query is a little too complicated or a lot of records in you tables?

[boywonder]

I use this app on XP OS also. It works OK on all the pcs except 2. All pcs are Dell p4 w/ 1GB memory. They all are running XP sp3 w/ Off 2003. I just need help with why it is giving me the below error:

Function is not available in expressions in query expression 'DateDiff("d",[cone fees],Date())'.

[weekend00]

It's a Date/Time format. I'm using Access 2003. I don't see reference under the Tools menu.

check reference under the Tools menu in Visual Basic window, not in Access main windows.

[boywonder]

I looked there and did not find any references missing.

[jzwp11]

I'm not sure what to tell you. One option is to create a new database and import everything into the new database.

By the way, is this database used by multiple users? If so, do you have the database split into front and backends?

[boywonder]

It is a front and back end and it is used by only user at a time. I will try the option. Thanks

[boywonder]

jzwp11 it worked. Thanks alot. I created a new db and imported everything over and it started to work like it suppose to work. Do you think someone maybe moved or deleted a reference or ActiveX dll file or something? I started working for this company about 6 months ago and the two guys that we contracted had 24/7 access to all company data for at least a 3 month period when I started. But they knew eventually they would be locked out of the system. They were the guys that developed the program, but when I asked about this problem, they were like its the computer and not the Access program.