473,320 Members | 1,922 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Simple query, difficulty with date

I have a form where a user enters a date that I will then base a query
on. In the past, I have simply placed Forms![Form_Name]![Field_Name]
into the criteria for a query and it runs as long as the form is open.
The recordset is then filtered based on what was entered in the form.
However, this strait forward approach seems to break when the form
value to be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field. The
recordset always comes up empty because I'm guessing that it is reading
the date on the form as a string instead of a date.

How do I convert my form value to a date in the criteria selection for
a query so that it will run like any other selection criteria I have
handled this way?

Thanks!

Nov 13 '05 #1
3 2086
bc******@jeffco.k12.co.us wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I have a form where a user enters a date that I will then base
a query on. In the past, I have simply placed
Forms![Form_Name]![Field_Name] into the criteria for a query
and it runs as long as the form is open. The recordset is then
filtered based on what was entered in the form. However, this
strait forward approach seems to break when the form value to
be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field.
The recordset always comes up empty because I'm guessing that
it is reading the date on the form as a string instead of a
date.

How do I convert my form value to a date in the criteria
selection for a query so that it will run like any other
selection criteria I have handled this way?

Thanks!

Dates need to be delimited with # in the query criteria

e.g. "#"+Forms!myform!mycontrol + "#"
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2
I wish that was it... that was actually one of the first things I
tried. In going against an unbound field, I don't think I can avoid
converting the string to a date. When I based the form on an date
field from an underlying (dummy/test) table,
Forms![MyForm]![Field_Name] worked just fine (as I expected it to work
the first time). Therefore, at least that experiment proved that it
is a data type issue. A date format in an unbound text field is not by
recognized by access as a date even when put between two #'s.

I guess I'd better start writing my converts!...

Nov 13 '05 #3
<bc******@jeffco.k12.co.us> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I have a form where a user enters a date that I will then base a query
on. In the past, I have simply placed Forms![Form_Name]![Field_Name]
into the criteria for a query and it runs as long as the form is open.
The recordset is then filtered based on what was entered in the form.
However, this strait forward approach seems to break when the form
value to be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field. The
recordset always comes up empty because I'm guessing that it is reading
the date on the form as a string instead of a date.

How do I convert my form value to a date in the criteria selection for
a query so that it will run like any other selection criteria I have
handled this way?


Parameters are (usually) resolved by Access on-the-fly and sometimes Access gets
it wrong. You can however open the parameters dialog in the query designer and
explicitly tell Access what DataTypes the parameters are. In your case you
would enter the parameter...

Forms![Form_Name]![Field_Name]

....with a DataType of DateTime.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
14
by: signaturefactory | last post by:
I am trying the following query in and oleDbCommand: SELECT PartLocations.LocationName, Sum(PartsJournal.Quantity) AS SumOfQuantity, PartsJournal.PartsLotNumber FROM PartLocations INNER JOIN...
4
by: Akinia | last post by:
Hi every body I've a little problem with my query, I can't figure it out. I've three tables: Table A (EmployeNr, Date, Code) Table B (EmployeNr, Date, Code) Which is an historic of Table A...
2
by: RBohannon | last post by:
I have a report with most fields populated by a query. However, some of the fields are variable in such a way that their values cannot be queried from a table. At present the values for these...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
1
by: Mike Cooper | last post by:
Hi everyone, This is a tough one. I have a database full of solicitations, identifying a customer and recording initial call, first followup, second followup, etc. My boss want to be able to...
5
by: Paul | last post by:
Hi This has taken me ages to work out but i'm sure there is a simple explanation and somebody here will be able to help. I'll talk you through it as best i can: 1. User enters dates on an...
3
by: Don Sealer | last post by:
I'm guessing this is pretty simple however not simple enough for me. I'm developing a database to track expenses, income, banking transactions, etc. I have a very simple query with four fields,...
2
by: Dinesh | last post by:
Hi experts, I am working on SQL Server 2005. Now i have to write a query which will extract some information from a table. My main table is having few columns supose 3 columns. EmpID ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.