Using [FORMS]! In query but also using BETWEEN? 1

[Stevehewitt]

Guys,

I am trying to create a normal query with a "DateCompleted" field. Users will want to enter in various criteria for this, but due to constraints within the reporting I need to do this cannot be a parameter query. Instead I will need to base the criteria from data entered into a unbound field on a form that is open.
For this I would be looking at using

[Forms]![frmFormName]![txtTextBoxName]

However, I need to have the query use "BETWEEN" in the criteria.

E.G. A user wants to search for all records between 01/01/2005 and 01/05/2005. Obviously in a normal query this would be:
BETWEEN #01/01/2005# AND #01/05/2005#

But as I am basing the criteria of the query on a open form using the FORMS! function, how would I go about adding the between command into the FORMS function?

Hope this makes sense!

Thanks in advance,


Steve.

 

[Crowley16]

you don't, you just call the [forms]!... directly from your query, and you don't need the ## limits if you've formatted the textbox as date, note, use medium date format since there's a date formatting difference in sql...

--------------------
Procrastinate Now!

 

[Stevehewitt]

OK.

I'm a bit confused! (sorry!)

I need the one criteria field to search between two ranges that are in two seperate fields on a open form.
I'm trying something like:

BETWEEN ([Forms]![frmTest]![txtStartDate]) AND ([Forms]![frmTest]![txtEndDate])

However this is not returning any values.

Can the I call data from a textbox in a open form as the criteria for the BETWEEN function in a query?

Thanks,


Steve.

 

[Crowley16]

yes, that's perfectly acceptable...

you say that you are not getting any values back, but are you getting an error message?

if you're not getting an error message, then could it be a logical problem, rather than syntactic problem?

check your formatting of the text box, use "medium date" throughout (this is very important), check that the dates are actually filled in, and they are correct...

--------------------
Procrastinate Now!

 

[PHV]

You may add the following as the 1st line of your SQL code:
PARAMETERS [Forms]![frmTest]![txtStartDate] DateTime, [Forms]![frmTest]![txtEndDate] DateTime;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Stevehewitt]

Thanks for your help guys. The data in the table that is going to be queried is only a short format date - would this make any difference?

Thanks,

Steve.

 

[Stevehewitt]

Guys,

When I use:

Between #08/04/2005# And #08/04/2006#

As the criteria it works fine.

If I use

BETWEEN ([Forms]![frmTest]![txtStartDate]) AND ([Forms]![frmTest]![txtEndDate])

Then I still get nothing. However if I use

[Forms]![frmTest]![txtStartDate]

Then it will display the correct data.

 

[Stevehewitt]

Unbloody believeable.

As you correctly stated Crowley16, it was a logical error and nothing to do with the Syntax. I forgot to rename the EndDate text box from the default (text21)!!!

AAAHHHHH!!

Evidentally I need a coffee and and smoke break to kick my brain into gear.

Have a star for confirming that the syntax was correct and for your patience!

Cheers,


Steve.

 

[Crowley16]

lol, it's an error that many of us have made often, and still make often...

part of the fun of being a programmer...

p.s. it IS important to use the medium date format.

SQL uses as the default "mm/dd/yy" whereas vb/access uses the native windows default which can be different, esp. if you don't live in the us...
When I first came across that little feature, it took me AGES to figure out what the problem was...

--------------------
Procrastinate Now!

 

[Stevehewitt]

Interesting! I didn't know that. I'm in the UK and there's not plan to upgrade the DB to SQL or similar so I'll leave it as it is for now as the app is used internally so all the PC's should be on the same format.

If not it will give IT something to do!

Thanks again for your help - appreciated.

Steve.

 

[Crowley16]

when I say sql, I meant the jet sql that access uses...

which means ANY sql statement you generate will default to that format...

however Any forms and table formatting will be the uk one...

--------------------
Procrastinate Now!

 

[Stevehewitt]

Ah - I'm with you. Point taken!

Cheers again - and thanks!


Steve.