This Expression is typed incorrectly....

[Moxioron]

Hello all.

Okay. I am trying to build a query that captures the count and sum of amount from another query.

Expr1 = Expr1: DateDiff("d",[DepDate],[Date])

I get the expression is typed incorrectly or too complex message.

If I just group by Expr1 all by itself I get the same message. Any thoughts or advice? Thanks for your help.

Here is my SQL:

SELECT [Qry_Incoming Returns - By Account (Detail)].Expr1, Count([Qry_Incoming Returns - By Account (Detail)].Expr1) AS CountOfExpr1, Sum([Qry_Incoming Returns - By Account (Detail)].Amount) AS SumOfAmount
FROM [Qry_Incoming Returns - By Account (Detail)]
GROUP BY [Qry_Incoming Returns - By Account (Detail)].Expr1;

 

[james_lankford]

DateDiff ( "d", [DepDate], Date() )

 

[Moxioron]

Sorry for the confusion. The expression works fine. I am trying to take the query Qry_Incoming Returns - By Account (Detail) and build another query that groups expr1 and am getting the 'This Expression is Typed Incorrectly' message.

 

[xenou]

If [DepDate] is a parameter make sure you explicitly enter it in the parameters of the query where it is originally used as a DateTime value. This is in the query design menu, or in the SQL at the very top:

PARAMETERS [DepDate] DateTime;
SELECT blah blah blah ... FROM blah blah blah;

 

[james_lankford]

Sorry for the confusion. The expression works fine. I am trying to take the query Qry_Incoming Returns - By Account (Detail) and build another query that groups expr1 and am getting the 'This Expression is Typed Incorrectly' message.

the reason I gave my reply is because this

DateDiff("d",[DepDate],[Date])

does not work for me

I tried it in Access 2003 and its asking for a parameter named Date

if I take the [] away from Date, it puts them back in and still asks for a parameter named Date

The only way I could get it to work was to write it as
DateDiff( "d", [DepDate], Date() )

so its weird that you say it works for you
I don't understand

 

[SydneyGeek]

If Date is a field name, Access may be getting confused because of the inbuilt Date function.
There are are number of reserved words that you should steer clear of as names for any objects or variables in your database. Run a search and you should find a few links. And if you *do* have a Date field, you should rename it.

Denis

 

[Moxioron]

Here you go...thanks for your help.

Option Compare Database
Public Function convertfile2to1()

Dim FH As Integer
Dim FH2 As Integer
Dim strPath As String
Dim strPathOut As String
Dim strLineIn As String
Dim strLineOut As String
'myCheck = MsgBox("Are you sure you want to convert CFS ATM INTRADAY REPORT.rpt to a 'one line' Text File?", vbYesNo)
'If myCheck = vbYes Then
'input file path
strPath = "U:\My Documents\Strategic Initiative\Michael's dumb report\actest.rpt"
' output path for new file converted to 1 line transaction
strPathOut = "U:\My Documents\Strategic Initiative\Michael's dumb report\CFS ATM Intraday.txt"
FH = FreeFile
Open strPath For Input As #FH
FH2 = FreeFile
Open strPathOut For Output As #FH2
Do
' get first line
Line Input #FH, strLineIn
strLineOut = strLineIn
' get second line
Line Input #FH, strLineIn
strLineOut = strLineOut & " " & strLineIn


Print #FH2, strLineOut


Loop Until EOF(FH)
'Else
'MsgBox "actest Text File conversion cancelled", vbOKOnly
'Exit Function
' End If
'MsgBox "CFS ATM Intraday.txt File File conversion complete."
Close #FH
Close #FH2
End Function