DateSerial formula error (invalid syntax) on a form

[smile]

Hi, I have a form with 2 unbound textboxes. I'm going to use them to provide query between criteria for my queries.

I need to calculate the first and last day of the month:

1st textbox
First day of month

=DateSerial(Year(Now), Month(Now), 1)

2nd textbox
Last day of month

=DateSerial(Year(Now), Month(Now) + 1, 0)

I get error "the expression you entered contains invalid syntax".

Please help.

 

[Rabbie]

Try using Date() instead of Now

BTW I have just tested your code in A2003 and it worked correctly.

 

[missinglinq]

I really don't know what to tell you. The syntax as given here is correct. Copying and pasting it into a form (to make sure there wasn't a stray dot that I couldn't see in the code post) they both function as expected. Where do you have the code placed?

Are these boxes being populated when the form opens? If not, how/where are they being populated?

Are you sure these lines are causing the problem?

 

[smile]

Tried to place formulas without Now, and got different error:

expression entered contains wrong number of arguments.

I paste them in design view inside unbound text box. I wan't them to work (diplay date) when I open my form.

My decimal separator is "," and date format is Y.M.D perhaps this why it does not work?

 

[Rabbie]

Can you post your DB because both Linq and I have got your code to work.

 

[Brianwarnock]

I'm not sure where Snmile is working, ie whether he is permitted different formats to us but I need 2 or 4 y in the date Format to get a sensible result, but, althougth I wouldn't use Now , this works

=Format(DateSerial(Year(Now()),Month(Now()),1),"YYYY.M.D")

Brian

 

[smile]

sure I can, here it is:

 

[Rabbie]

Brian, Both Smile's formulas as originally posted worked straight away in A2003 when I copied and pasted them into textboxes on a form.

 

[Brianwarnock]

Smile
remove the " " from round the formulae

E.G. =DateSerial(Year(Date()),Month(Date())+1,0)

Brian

 

[Brianwarnock]

Brian, Both Smile's formulas as originally posted worked straight away in A2003 when I copied and pasted them into textboxes on a form.

Just wanted to test the date formatting.Why has the forum put a space between my YYYY ?

Brian

 

[missinglinq]

When I remove the quotation marks (they shouldn't be there) and add the preceding equal sign to the last of month textbox Control Source (which you've apparently removed) so that you have

First of month

=DateSerial(Year(Now), Month(Now), 1)

Last day of month

=DateSerial(Year(Now), Month(Now) + 1, 0)

your database, once again, works as expected. My next guess would be that you have a missing reference.

Here are Doug Steele's detailed instructions on how to troubleshoot reference problems:

*** Quote ***

Any time functions that previously worked suddenly don't, the first thing to suspect is a references problem.

This can be caused by differences in either the location or file version of certain files between the machine where the application was developed, and where it's being run (or the file missing completely from the target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the Debug Window, using Ctrl-G, provided you haven't selected the "keep debug window on top" option). Select Tools | References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected. (NOTE: write down what the references are before you delete them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Just so you know: the problem will occur even if the library that contains the specific function that's failing doesn't have a problem.

**** End Quote ****

 

[Rabbie]

Just wanted to test the date formatting.Why has the forum put a space between my YYYY ?

Brian

It does that sometimes. Some sort of bug I think

 

[smile]

Smile
remove the " " from round the formulae

E.G. =DateSerial(Year(Date()),Month(Date())+1,0)

Brian

on my form frm_laikotarpis I write:
remove "" from formulas as I could not save otherwise.

Did you miss it? I mean I said I got syntax error its obvious I can't save the form with formula in it. What good is form with empty textboxes?

So I saved formulas with quotes thinking it's formatting problem as I can't save the form with formula in it because I got xyntax error.

Now if I remove "Now" in my formula I get different error: expression entered contains wrong number of arguments.
for example: =DateSerial(Year(), Month(), 1)

Now my computer has access 2007, My decimal separator is "," and date format is Year Month Day (example: 2008.01.01) perhaps this why it does not work?

 

[smile]

Well I made a test and opened my original database I attached to the thread above on anothe machine with regional settigns set to English (UK)

The form works but formulas were changed by access to:

=DateSerial(Year(Now()),Month(Now()),1)

=DateSerial(Year(Now()),Month(Now())+1,0)

Don't know why?

Then I copied the working copy of the db to my PC where I had problems. Formulas were changed to:

=DateSerial(Year(Now());Month(Now());1)

=DateSerial(Year(Now());Month(Now())+1;0)

Now it WORKS !!

 

[Brianwarnock]

Did you miss it? I mean I said I got syntax error its obvious I can't save the form with formula in it. What good is form with empty textboxes?

Don't get ratty with me I'm only trying to help, when I removed the "" it worked.

Brian

 

[missinglinq]

Ditto for me. And I have to assume there's no corruption involved, or it wouldn't work for Brian and myself when we ran the copy you posted.

Have you checked for missing references as I suggested?

Of course removing Now() from the formulas gave you errors; you then have no date for the formula to process.

 

[smile]

Does anobody know why access added aditional ")" anyway?

 

[Brianwarnock]

No apology, no response to linq's questions, just a demand for more information. you sure know how to keep people on your side.
However I am a tolerent guy so the answer is intellisense. Now() is the correct format, and in SQL the system can correct certain errors, which is why one should never use reserved words asobject names or have spaces in object names.

Brian

 

[smile]

No apology, no response to linq's questions, just a demand for more information. you sure know how to keep people on your side.
However I am a tolerent guy so the answer is intellisense. Now() is the correct format, and in SQL the system can correct certain errors, which is why one should never use reserved words asobject names or have spaces in object names.

Brian

Sorry, I did not reply to your message. Your original sollution on post 6 did not work and as I understand it's a problem of regional settings not access.

For some reason nobody suggested that it could be regional settings so I was stumped at it for 2 days. Sorry if my actions were rude or anything I did not mean it.

 

[smile]

I want to add 2 buttons to my form

1st to update formulas that previous month dates would be displayed
2nd to revert back to current month

Also on the fly date change would be nice. Now I can't enter my own date range.

Another question I have is how to make unbound textbox dhow data from sum (total) query?
Now I use =Nz(dlookup("my_qry_field_name";"qry_my_query_name";"my_qry_field_name"="my_qry_field_name");0)

It works, but I would like to avoid it for performance issues or is it OK to use if it is a total query with result only? Any better way to do this is appreciated.