How can I make an expression which refferes to a specific date on the current year? (1 Viewer)

[Inkognito]

Hi! I have a little VBA-line that needs a bit of changing.
It is part of a bigger procedure that calculates how many boats a company has on their docks.
With this code though, I would have to manually go in and change the date every year.
It should preferabelly be counting from the date April 1st on the current year.
Like #01-04# + Format(Date, "yyyy\") or something (I know this was way off, but you might see what I'm trying to do)

Ut_telleverk = Ut_telleverk + DCount("[Medlemsnr2]", "bestilling_ut", "Nz([Medlemsnr2]) <> chr(48) AND Nz([Dato]) >= #01-04-2009# AND Nz([Dato]) <= Date()")

Help greatly appreciated


edit:
This is the part that needs changing to April 1st, current year instead of 2009: #01-04-2009#

 

[boblarson]

How about this:

AND Nz([Dato]) Between DateSerial(Year(),4,1) AND Date()

 

[Inkognito]

Ut_telleverk = DCount("[Medlemsnr]", "bestilling_ut", "Nz([Medlemsnr]) <> chr(48) AND Nz([Dato]) Between DateSerial(Year(),4,1) AND Date()")

This line gives me this error though:
"Wrong number of arguments used with function in query expression 'Nz([Medlemsnr]) <> chr(48) AND Nz([Dato]) Between DateSerial(Year(),4,1) AND Date()'"

 

[Inkognito]

Dim Ut_teller_dato as Date
Ut_teller_dato = DateSerial(Year(), 4, 1)

This gives me "Argument is not optional"

Could the DateSerial usage be inaccurate?

edit: DateSerial(Year(Date), 4, 1) works, but now I get "You cancelled the previous operation" when I run dcount...

Still works with #04-01-2010#, but not DateSerial(Year(Date), 4, 1)

 

[JamesMcS]

I think you have to enclose each condition in your dcount in brackets but I'm not sure - try

(Nz([Medlemsnr]) <> chr(48)) AND (Nz([Dato]) Between DateSerial(Year(),4,1) AND Date())

The error "You canceled the previous operation", I think, means a typo in your code.

 

[Inkognito]

No difference when enclosing it in brackets...
Have to use Year(Date) and not Year() though.

It is working when I am using #Date# instead of Dateserial, so if it is a typo, it would have to be in the DateSerial-part somehow...

Must be another way of creating a date of the current year?


Edit:
No matter how I make the date, I get "You cancelled the previous operation", while #01-04-2010# still works, I don't get this...

 

[JamesMcS]

try year(now())

 

[Inkognito]

Both Year(now()) and Year(Date) works fine, no problem there

But I need to make the date 01/04/[current year]

I just can't seem to get my dcount sentence to accept any other input that a date enclosed in #'s.

 

[c_smithwick]

When used in a query, the second argument of the Nz function should always be used. From The Access help file:

The Nz function has the following arguments.
First argument - variant A variable of data type Variant.

Second argument - valueifnull [Optional] (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string. Note If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,
If the value of variant isn't Null, then the Nz function returns the value of variant.

 

[Inkognito]

JamesMcS, I just tried actually using Year(Now) instead of Year(date) in the actual sentence, and it worked!!
I didn't even try to use it in the actual sentence the last time, as Year(date) and Year(now) made the same output in an msgbox, so I figured they would be the same.

Thanks a lot, JamesMcS and boblarson!!

c_smithwick, thanks for your tip too, I figure it could have worked if I included that valueifnull argument somewhere as well, but I had no idea how to use it

 

[JamesMcS]

Nice one, happy to help!