• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

DateAdd in query

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

0
databarracks
Asked:
databarracks
  • 11
  • 9
  • 8
2 Solutions
 
Simon BallCommented:
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

0
 
Simon BallCommented:
the myToDate field can be visible = false.  user won;t see it but query will be able to get to its value.
0
 
OP_ZaharinCommented:
- i try to simulate by create a table call Table1 with a column Dates.
- then i use CDATE function on Date() and DateAdd(), and the output is as expected based on parameter insert for number of days ("d"):

SELECT * FROM Table1
WHERE Dates2 BETWEEN CDAte(Date()) AND CDate(DateAdd("d", ([forms]![frmDate]![cmbDays]),Date()))

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Simon BallCommented:
nice one!  so the query errors because its trying to do "between" on text string returned ?
0
 
OP_ZaharinCommented:
- yeah i believe so Sudonim :)
- databarracks also need to check that the column that he is comparing is a Date datatype
0
 
databarracksAuthor Commented:
The data type is date I can confirm that much. I am giving your tips a go as we speak
0
 
Simon BallCommented:
mine will help with your subforms issue but that CDATE() zaharin is Gold!
0
 
databarracksAuthor 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

0
 
databarracksAuthor Commented:
Please find attached my sample DB
TestlblDB.accdb
0
 
OP_ZaharinCommented:
- i'm on my way back home and will look into your file.
- have you try to run the sql in Access query window?
0
 
OP_ZaharinCommented:
- 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
0
 
OP_ZaharinCommented:
Sudonim, thank you so much for the compliment! but sadly, it still doesn't work for our friend databarracks here...
0
 
Simon BallCommented:
need to see the form, its not in your db OPZ.

i too only have 2007 DBarr.
0
 
databarracksAuthor Commented:
Hi guys doesn't need to be in 2010, just the premise is required
0
 
OP_ZaharinCommented:
- we can't open yours on our 2007 version
0
 
Simon BallCommented:
i get a weird error doing the dateadd in the query.

but if i do the date add under a button on the form...

Private Sub Command2_Click()
Me.Text5.Value = DateAdd("d", Me.Text3.Value, Date)
End Sub

and then refer to the form field in the between,..

it works..

Between Date() And CDate([forms]![fmrdate]![text5].[value])

result:
Field1
20/04/2011
21/04/2011
22/04/2011
23/04/2011
24/04/2011
25/04/2011
26/04/2011
27/04/2011


edit:

typed it again from the working query - and it sowrks now:

Between Date() And CDate(DateAdd("d",[forms]![fmrdate]![text3].[value],Date()))


Databrracks.mdb
0
 
Simon BallCommented:
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.
0
 
Simon BallCommented:
use builder to find the form and the field you need.
0
 
databarracksAuthor 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 :)
0
 
Simon BallCommented:
no.  expert programmer occasionally run into random glass wallls :)

i couldn;t open your copy either... 2010 <> 2007 i think.
0
 
databarracksAuthor 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
0
 
databarracksAuthor 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.
0
 
OP_ZaharinCommented:
ah, not even a split point? haha ;)
0
 
Simon BallCommented:
yeah this is true.

op should get split points for his cdate
0
 
databarracksAuthor 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?
0
 
Simon BallCommented:
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.
0
 
databarracksAuthor Commented:
Good work
0
 
OP_ZaharinCommented:
thank you so much guys :)
really appreciate it!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 11
  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now