Solved Reformulate an expression with multiple criteria (Numbers and dates)

[Falcon88]

hii al dears

i have this expression :

PersonA: CLng(nz(Dcount("OrderID";"Q105";"InsurCmpName=13" & " AND [OrderDate]>= #" [FirstDayVal] & "# AND  [OrderDate] <=" # [LastDayVal]);0))

Can you hlep me to correct this expression ?

 

[theDBguy]

What is it supposed to do? I think DCount() should either return a zero (0) or a higher number, so Nz() is probably not necessary.

 

[pbaldy]

Dcount("OrderID";"Q105";"InsurCmpName=13 AND [OrderDate]>= #" & [FirstDayVal] & "# AND [OrderDate] <= #" & [LastDayVal] & "#")

 

[Falcon88]

Dcount("OrderID";"Q105";"InsurCmpName=13 AND [OrderDate]>= #" & [FirstDayVal] & "# AND [OrderDate] <= #" & [LastDayVal] & "#")

it gives me wrong results
what the correct expression?

 

[Gasman]

Might want to put the criteria into a string variable, debug.print it until you get it correct, then use that in the function.
If you cannot get it correct, then you can post the output back here.

 

[pbaldy]

Wrong in what way? I think it's a valid expression now, so check your values.

 

[Gasman]

I dare say your dates are in the wrong format?

 

[Falcon88]

My dates formula is dd,mm,yyyy

 

[MajP]

Try wrapping in format.
"OrderID";"Q105";"InsurCmpName=13 AND [OrderDate]>= #" & Format([FirstDayVal],'MM/DD/YYYY') & "# AND [OrderDate] <= #" & format([LastDayVal],'MM/DD/YYYY') & "#"

 

[Falcon88]

Try wrapping in format.
"OrderID";"Q105";"InsurCmpName=13 AND [OrderDate]>= #" & Format([FirstDayVal],'MM/DD/YYYY') & "# AND [OrderDate] <= #" & format([LastDayVal],'MM/DD/YYYY') & "#"

very thanks , it sloved

 

[Gasman]

My dates formula is dd,mm,yyyy

I thought as much.

 

[Gasman]

In fact you have come across this issue before and used a format, defined as a constant?

append query based On other Query

all dears i have a table contains the dates of a year. in Q1 . if the day is friday i want to append as in [DayValue], 42 records . elese append 86 records . where starts with [From] , and ends with [TO] (Serially) append to OrderID and BillDate append to OrderDate in tblOrders

www.access-programmers.co.uk

 

[Falcon88]

In fact you have come across this issue before and used a format, defined as a constant?

append query based On other Query

all dears i have a table contains the dates of a year. in Q1 . if the day is friday i want to append as in [DayValue], 42 records . elese append 86 records . where starts with [From] , and ends with [TO] (Serially) append to OrderID and BillDate append to OrderDate in tblOrders

www.access-programmers.co.uk

Yes, I forgot, it was more than 7 years ago.

Greetings to you

 

[Gasman]

Yes, I forgot, it was more than 7 years ago.

Greetings to you

Well I have a pretty bad memory these days , but once the date issue bit me, I have always remembered it.