Date() frustration within query builder - please assist

[pilch74]

hi..

I have created an application which scans barcoded mail items and
injects results into a.n.other table.

As part of v2 I'm trying to offer even more usability/funtionality.

Basically at the end of the day when the operator produces his/her
'dayend' tasks I want to offer them a button on the main form which,
when clicked runs a query which shows a total for today of pieces of
mail type "blah" - in this case, there's only a choice of two mail
types. goneaways & prizedraws.

http://pastebin.com/m2f6bec45 - this works fine when manually entering
the date in the Criteria field within the Query builder - shows the
one result fine. However when I enter =Date() in the Criteria field it
doesn't show any results whatsoever.

Why is this and how can this be resolved without actually doing a
paramater query which I don't really want to do neither do I want the
operator to key in a date on the main form.

Can anyone help please? I'd be very greatful.

Regards,

Richard.

 

[George Nicholson]

However when I enter =Date() in the Criteria field it

doesn't show any results whatsoever.

From the posting on your link, when the query works you are converting your
[Stamp] field to text and comparing that to a (manually entered) text value.
I therefore suspect you just need to convert Date() to text in the same
format:

Criteria: = Format(Date(),"dd/mm/yy")



HTH,

 

[pilch74]

Thanks for the reply.

I've put into place what you suggested and the query works, see
http://pastebin.com/m3644de1e

With this now fixed hopefully I can move onto the last stage which is
to simply produce a count as per my original post (see extract below)

"I want to offer them a button on the main form which, when clicked
runs a query which shows a total for today of pieces of mail type
"blah" - in this case, there's only a choice of two mail types.
goneaways & prizedraws."

So, going on what I knowI have tried doing this within the query
builder field this way by changing

total pdraw no: Format$([Stamp],"dd/mm/yy")

to

total pdraw no: Count(Format$([Stamp],"dd/mm/yy"))

but Access is telling me about not being able to have an aggregate
function.

How do I resolve this?

Many thanks in advance.

Regards,

Richard.

However when I enter =Date() in the Criteria field it
doesn't show any results whatsoever.

From the posting on your link, when the query works you are converting your
[Stamp] field to text and comparing that to a (manually entered) text value.
I therefore suspect you just need to convert Date() to text in the same
format:

Criteria: = Format(Date(),"dd/mm/yy")

HTH,

hi..

I have created an application which scans barcoded mail items and
injects results into a.n.other table.

As part of v2 I'm trying to offer even more usability/funtionality.

Basically at the end of the day when the operator produces his/her
'dayend' tasks I want to offer them a button on the main form which,
when clicked runs a query which shows a total for today of pieces of
mail type "blah" - in this case, there's only a choice of two mail
types. goneaways & prizedraws.

http://pastebin.com/m2f6bec45- this works fine when manually entering
the date in the Criteria field within the Query builder - shows the
one result fine. However when I enter =Date() in the Criteria field it
doesn't show any results whatsoever.

Why is this and how can this be resolved without actually doing a
paramater query which I don't really want to do neither do I want the
operator to key in a date on the main form.

Can anyone help please? I'd be very greatful.

Richard.

 

[pilch74]

Hold the phone I've fixed it myself (wohoo).

I just needed to add another instance of the 'JobType' field inside
the Query Builder and change the TOTAL filed to 'count'.

It's working beautifully and here's a copy of the final SQL:
http://pastebin.com/m69b614ed

Regards,

Richard.

Thanks for the reply.

I've put into place what you suggested and the query works, seehttp://pastebin.com/m3644de1e

With this now fixed hopefully I can move onto the last stage which is
to simply produce a count as per my original post (see extract below)

"I want to offer them a button on the main form which, when clicked
runs a query which shows a total for today of pieces of mail type
"blah" - in this case, there's only a choice of two mail types.
goneaways & prizedraws."

So, going on what I knowI have tried doing this within the query
builder field this way by changing

total pdraw no: Format$([Stamp],"dd/mm/yy")

to

total pdraw no: Count(Format$([Stamp],"dd/mm/yy"))

but Access is telling me about not being able to have an aggregate
function.

How do I resolve this?

Many thanks in advance.

Regards,

Richard.

From the posting on your link, when the query works you are converting your
[Stamp] field to text and comparing that to a (manually entered) text value.
I therefore suspect you just need to convert Date() to text in the same
format:

Criteria: = Format(Date(),"dd/mm/yy")