If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using dates from forms as criteria
Hi,
I am fairly new to access so this migth be a simple problem to solve. I have a form and have two text boxes for dates. Start Date and End Date. I would like the input to be used as criteria in a make table query. I have looked around and the usual way would be to use "Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]" However, this gives me no records. I know there are records thougth because when I do not use the form for date entry but let Access prompt me for input it works fine. Can anyone help? Has it something to do with the formatting of the text fields? I have set them to 'Format: Short Date'. I have noticed that access puts '#' around dates when used as criteria in a query. Could that be the problem? I would apprciate any help. |
#2
|
|||
|
|||
using dates from forms as criteria
If you included the quote marks in your criterion string, remove them. It
should then work OK. HTH, Rob "the_dude" u30228@uwe wrote in message news:6ac2651185714@uwe... Hi, I am fairly new to access so this migth be a simple problem to solve. I have a form and have two text boxes for dates. Start Date and End Date. I would like the input to be used as criteria in a make table query. I have looked around and the usual way would be to use "Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]" However, this gives me no records. I know there are records thougth because when I do not use the form for date entry but let Access prompt me for input it works fine. Can anyone help? Has it something to do with the formatting of the text fields? I have set them to 'Format: Short Date'. I have noticed that access puts '#' around dates when used as criteria in a query. Could that be the problem? I would apprciate any help. |
#3
|
|||
|
|||
using dates from forms as criteria
Thanks very much for your help, Rob.
Unfortunately I used the quotation marks only for display puposes in this thread. I do not use them in my criteria. Any ideas what it could be? I have tried the same on a different PC with the same outcome. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#4
|
|||
|
|||
using dates from forms as criteria
Could be several things:
1. Parameter type ============== In query design, choose Parameters on the Query menu. Access opens the Parameters dialog. Enter 2 rows, like this: [FORMS]![MyForm]![StartDate] Date/Time [FORMS]![MyForm]![EndDate] Date/Time This helps Access understand the data type of the 2 parameters. 2. Field type ========= Presumably you have this "Between ..." statement in the Criteria row under a field. What is the data type of the field? Open your table in design view. It need to be a Date/Time field, not a Text field. If the field is a calculated field, such as: Date() + 30 enclose the expression in CVDate() so that Access treats it like a date, e.g.: CVDate(Date() + 30)) More info about that: Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html 3. Value of text box ============== When you type a date into the text box, Access updates the value when you *leave* the field. If the cursor has not left the text box since you entered the date, it may not have its Value yet. Tab out of the text box, and try again. 4. Dates with time component ======================= Date/Time fields can contain a date as well as a time. If there is a time component, records on the last date in the range won't match. To avoid this, use this as your criteria: = [FORMS]![MyForm]![StartDate] And ([FORMS]![MyForm]![EndDate] + 1) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "the_dude" u30228@uwe wrote in message news:6ac2651185714@uwe... Hi, I am fairly new to access so this migth be a simple problem to solve. I have a form and have two text boxes for dates. Start Date and End Date. I would like the input to be used as criteria in a make table query. I have looked around and the usual way would be to use "Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]" However, this gives me no records. I know there are records thougth because when I do not use the form for date entry but let Access prompt me for input it works fine. Can anyone help? Has it something to do with the formatting of the text fields? I have set them to 'Format: Short Date'. I have noticed that access puts '#' around dates when used as criteria in a query. Could that be the problem? I would apprciate any help. |
#5
|
|||
|
|||
using dates from forms as criteria
thank you allen,
i have set the parameters in the qry to date/time. the field is a proper date field and not calculated. It all works fine if i do not have the date fields on the form but let acccess prompt me (in criteria: Between [Start_Date] And [End_Date]) but for some reason it does not work when I have the date fields populated by the form? has anyone any ideas? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#6
|
|||
|
|||
using dates from forms as criteria
I may not understand your problem exactly, but it reminded me of a
situation I had in which I needed to find the pay record for a selected employee for a certain date. I could not get a match until I tried this: MyQuery = "SELECT * FROM PayRecords WHERE " _ & "EmployeeId = '" & cboEmployee.Value & "'" _ & " AND Format$(PayDate,'mm/dd/yyyy') = '" _ & Format(txtDatePaid, "mm/dd/yyyy") & "'" The key to the solution was that both dates had to be formatted the same way. I hope this helps Cordially, Robert |
#7
|
|||
|
|||
using dates from forms as criteria
Hello, what I use is a form with unbound combo boxes for start and end dates. In the query I use the following: =[forms]![report options]![txtdatefrm] And =[forms]![report options]![txtdateto] Enter the appropriate dates into the form and run the query. It will (or should) work a treat! -- How many buildings collapsed on 9/11? I can tell you the answer isn''t 2 !! "the_dude" wrote: Hi, I am fairly new to access so this migth be a simple problem to solve. I have a form and have two text boxes for dates. Start Date and End Date. I would like the input to be used as criteria in a make table query. I have looked around and the usual way would be to use "Between [FORMS]![MyForm]![StartDate] And [FORMS]![MyForm]![EndDate]" However, this gives me no records. I know there are records thougth because when I do not use the form for date entry but let Access prompt me for input it works fine. Can anyone help? Has it something to do with the formatting of the text fields? I have set them to 'Format: Short Date'. I have noticed that access puts '#' around dates when used as criteria in a query. Could that be the problem? I would apprciate any help. |
#8
|
|||
|
|||
using dates from forms as criteria
Time to ask Access what's going on.
1. Open the form open and type the dates into the 2 text boxes. 2. Open the query in design view. Copy the exact words: [FORMS]![MyForm]![StartDate] from the query statement to clipboard (Ctrl+C.) 2. Open the Immediate Window (Ctrl+G) 3. Type a question mark, space, and Paste (Ctrl+V). You should now have: ? [FORMS]![MyForm]![StartDate] Press Enter. Does it show the correct date? 4. Repeat for the EndDate, to show the result in the Immediate window. 5. Once both dates are verified, switch the query to Datasheet view. If the dates are correct, but the query shows no values, there's something wrong with the data. Are you showin 4-digit years here, so you can verify the dates are in the right century? It would also be a good idea to compact/repair the database (under Database Utilities on the Tools menu), just in case there is a bad index on the date field. If it still fails, post the full SQL statement from the query. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "the_dude via AccessMonster.com" u30228@uwe wrote in message news:6acb4db5aff9c@uwe... thank you allen, i have set the parameters in the qry to date/time. the field is a proper date field and not calculated. It all works fine if i do not have the date fields on the form but let acccess prompt me (in criteria: Between [Start_Date] And [End_Date]) but for some reason it does not work when I have the date fields populated by the form? |
#9
|
|||
|
|||
using dates from forms as criteria
Set the format of your textboxes to ShortDate on the form. Access may not
be interpreting your entry as a date. -- Joan Wild Microsoft Access MVP the_dude via AccessMonster.com wrote: thank you allen, i have set the parameters in the qry to date/time. the field is a proper date field and not calculated. It all works fine if i do not have the date fields on the form but let acccess prompt me (in criteria: Between [Start_Date] And [End_Date]) but for some reason it does not work when I have the date fields populated by the form? has anyone any ideas? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
#10
|
|||
|
|||
using dates from forms as criteria
Ah, it works now. The check in the immediate window worked a treat and showed
my error... even thougth I was sure that my spelling was correct before I started experimenting to try and track down the error. Thank you everyone who helped. I appreciate it very much and I hope I can be off help sometime as well. Kind regards -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200612/1 |
Thread Tools | |
Display Modes | |
|
|