We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

DateAdd in query

Medium Priority
1,186 Views
Last Modified: 2013-11-27
Hi there,

I am trying to apply criteria in a date field within my query using a between and dateadd function as per attached code. However I get an error saying that "This expression is typed incorrectly, or it is too comples to be evaluated etc....."

I understand that it is the way that I am referencing the control of the form on my query causing this but I don't know how to do it. I have used the below code which kind of works but the parameter pop up appears on my form everytime I requery. Ultimately my reference to the form will be longer as I need to reference a subform within two navigation forms which in Access 2010 requires a lot of drilling down.

Between Date() And DateAdd("d",[cmbDays],Date())

Not too worried until I know the basic construct of the proper way of referencing a control on a form, as my goal is to allow the user to determine how many days ahead (1-7 days) they would like to filter the form based on their choice.
Between Date() And DateAdd("d",([forms]![frmDate]![cmbDays]),Date())

Open in new window

Comment
Watch Question

Simon BallChief information Officer

Commented:
i got this bit working:

Me.Text5.Value = DateAdd("d", Me.Text3.Value, Date)

so i named the form ( wrong) and made my days field a combo..

Me.Text5.Value = DateAdd("d", Forms!fmrDate!cmbDays, Date) and this outputs the correct date into my text box..

can you write the "to" date onto your toplevel form, and then have the query look at that field?

where xdate between date() and forms!myform!myToDate.value

Simon BallChief information Officer

Commented:
the myToDate field can be visible = false.  user won;t see it but query will be able to get to its value.
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Simon BallChief information Officer

Commented:
nice one!  so the query errors because its trying to do "between" on text string returned ?
Top Expert 2011

Commented:
- yeah i believe so Sudonim :)
- databarracks also need to check that the column that he is comparing is a Date datatype

Author

Commented:
The data type is date I can confirm that much. I am giving your tips a go as we speak
Simon BallChief information Officer

Commented:
mine will help with your subforms issue but that CDATE() zaharin is Gold!

Author

Commented:
I have tried zaharin's method and still getting the same error? I have modelled my table on his, this is my syntax:
SELECT  *
FROM Table1
WHERE ((([Dates2]) Between CDate(Date()) And CDate(DateAdd("d",([forms]![frmDate]![cmbDays]),Date()))));

Open in new window

Author

Commented:
Please find attached my sample DB
TestlblDB.accdb
Top Expert 2011

Commented:
- i'm on my way back home and will look into your file.
- have you try to run the sql in Access query window?
Top Expert 2011

Commented:
- ah.. sorry databarracks. i only have Access 2007 here at home. could you save it as 2007?
- i pasted the sql and run it again from the query window (not form), and it still works. i attached my sample db for your reference. doubleclick the QueryDate from the left pane to run it. to view the source, right click and select SQL view.

 Database1.accdb
Top Expert 2011

Commented:
Sudonim, thank you so much for the compliment! but sadly, it still doesn't work for our friend databarracks here...
Simon BallChief information Officer

Commented:
need to see the form, its not in your db OPZ.

i too only have 2007 DBarr.

Author

Commented:
Hi guys doesn't need to be in 2010, just the premise is required
Top Expert 2011

Commented:
- we can't open yours on our 2007 version
Chief information Officer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Simon BallChief information Officer

Commented:
could the error you are getting be occuring because of any other part of the query, rather than this bit?

try making a new query and building it from scratch again.

use builder to fint he form and the field you need.
Simon BallChief information Officer

Commented:
use builder to find the form and the field you need.

Author

Commented:
Ok I will give it a go startingn from scratch. Did you manage to look at my sample to see where I am giong wrong. I honestly thought that it would be simple but I was obviously mistaken, I am clearly an amateur :)
Simon BallChief information Officer

Commented:
no.  expert programmer occasionally run into random glass wallls :)

i couldn;t open your copy either... 2010 <> 2007 i think.

Author

Commented:
Oh I see, I only noticed that you provided me with a working sample in ID:35433086, apologies I missed it completely. I will try accommodating your sample into my working sample and see what happens. Please bear with me for a fwe minutes whilst I test it.

I think it might work

Author

Commented:
I have used your code and it has worked like a charm, I will just hide some of the text boxes so it looks neat and tidy but thank you once again for your help.
Top Expert 2011

Commented:
ah, not even a split point? haha ;)
Simon BallChief information Officer

Commented:
yeah this is true.

op should get split points for his cdate

Author

Commented:
Sorry guys not familiar with the point sharing system. Is it too late for me to do this, or can I still apply it and if so how?
Simon BallChief information Officer

Commented:
i went to the question at the top, hit "request attenion2 and ask for a moderators help,

stating i agree that their should be a points split.

Author

Commented:
Good work
Top Expert 2011

Commented:
thank you so much guys :)
really appreciate it!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.