Dcount help in VBA

[tozey]

Hi guys

I am looking for an answer to a Dcount query. I am pretty new to it and have look through the internet but cannot get what I need to know.

I was wondering if you could help?

Thanks in advance

I want my results to only show if they are less than the date I have specified in the Variable

PRIORITY Dim$

PRIORITY = "06/08/2010"

RESULT = DCount("[AccountNumber]", "MainTable", "[CampaignTitle] = ‘Mission’ and [CurrentStatus] = 'unallocated' and [LoadedDate] < PRIORITY ")

Also, It is possible to have the variable get the date from a form rather than having to update it in the module? Come to the end of the module I am looking to have many results all looking at the same date so only want to have to change it once.

Hope this makes sense

Thanks for your help

Tozey

 

[tozey]

Just realised I typed the Variable in wrong on the post (not in vba)

Dim PRIORITY$

doh!

 

[vbaInet]

RESULT = DCount("[AccountNumber]", "MainTable",  "[CampaignTitle] = ‘Mission’ and [CurrentStatus] = 'unallocated' and  [LoadedDate] < #" & PRIORITY & "#")

 

[tozey]

Excellent, thank you.

This helps no end.

Alex

 

[vbaInet]

No problemo!

 

[tozey]

Sorry, I am stuck again, last question honest..

How about if I want to change the < #" & PRIORITY & "#

to

< date()-7

I am just not sure how to write this and everything I try doesnt seem to work?

Thanks again

Alex

 

[vbaInet]

Remove the hash (#) and put Date()-7 in place of Priority.

 

[tozey]

Hello, I need a little more help.

I now need to include a date difference number to identify my cases.

Here is how I have written it

Result = DCount("[AccountNumber]", "Main Table", "[CampaignTitle] = 'Mission' and [CurrentStatus] not like 'Unallocated' and [LoadedDate] < #" & PRIORITY & "# and [DateInitiallyWorked] - [LoadedDate] > 7 ")

I have tried many combinations from info I have had for you guys and the web but just cannot get it.

hope you can help?

Thanks for your help, it is very much appreciated.

Alex

 

[tozey]

Is anyone able to help?

thanks again.