datepart function 1

[Guest_imported]

Hi all,

I'm still having this stupid syntax problem with the
datepart function in the help function it is displayed
datepart("m",[blablabla]=1) the compiler gives an error on , and says it should be ;
anyway can anybody help me with this simple result that I want namely a query which gives me all the records with month 2 for example in a date field.

thnx in advance

perto

 

[kathryn]

Is this:

datepart("m",[blablabla]=1)

a typo? it should be

datepart("m",[blablabla])=1

Kathryn

 

[Guest_imported]

well I thought too that that should work but I get a syntax error on the , when I replace it with a ; I don't get the syntax error but it doesn't work ether.

 

[kathryn]

Why don't you post the code that this line is in?

Kathryn

 

[Rohdem]

Datepart is a function that returns a value. It must be assigned to something, i.e.
somefield = datepart("m", [blablabla])

If you are trying to create a specific date in a variable, use date serial:
blablabla = (1999,1,31)

If you want to change just the month value of the field, do something like this:
dim year as integer
dim day as integer

year = datepart("yyyy", [blablabla])
day = datepart("d", [blablabla])
blablabla = dateserial(year, 1, day)




Mike Rohde
rohdem@marshallengines.com

 

[MelF]

O.K. I have a Customers table that includes the fields OrderDate and #ofComputersSold. I want to find out the #ofComputers sold per month. I've tried to use the DatePart function to do this. In the OrderDate field in my query, I put the criteria DatePart("m",[OrderDate]) And I put the Sum function in the #ofComputersSold. It returns 0 records. Am I not doing this correctly?

Thanks!!

 

[Rohdem]

The problem is you need to create an expression in the query that has just the month. Right now you are comparing your order date, 1/4/01 for example, to the month of the order date, in the example, 1. They don't match. Create an expression in the field line of the query like this...

Month: DatePart("m",[OrderDate])

Then set the total property to group by. Now put in your sum field on the #ofcomputersSold. This should give you the totals by month.
Mike Rohde
rohdem@marshallengines.com

 

[MelF]

MIKE - Thanks for responding! I put
Month: DatePart("m",[OrderDate]) in as the third field line of the query. It's total property is Group By. The Sum field is on the #ofComputersSold. It's now returning the month number in the recordset, but not totalling them by month. My recordset now looks like this:

Month OrderDate #ofComputers

Month OrderDate SumOf#ofComputerstobeRealMed-Enabled at this Site
1 1/1/2001 5
1 1/2/2001 5
1 1/3/2001 54
1 1/4/2001 62
2 2/1/2001 13
2 2/2/2001 15

I want it to say 126 enabled in the "1" month, and 28 enabled in the "2" month. ???????????

Thanks!!

 

[Rohdem]

Put the 'month' field in the first column of your query. Take out the other date field so all you have is the month column and the sum column.
Mike Rohde
rohdem@marshallengines.com

 

[MelF]

MIKE - THANK YOU, THANK YOU, THANK YOU!! It worked!!

Thanks again!!!!!!