Full Version: problem with date function involving form and query
UtterAccess Forums > Microsoft® Access > Access Date + Time
hi all,
I am building a database that stores data as months and years.
ie. each record has a range begin and a range end and entered using a dropdown box for month and year and then the date serial is generagted in the query to make it the first day of the chosen month.
eg query field
PeriodRangeBegin: DateSerial([Admin3_PeriodRangeBeginYear],[Admin3_PeriodRangeBeginMonth],1)
i am trying to set up a form to filter through my records to have added some criteria to the query field which is:
and similar for the range end
in my form i have a range begin and a range end pairs of drop down lists for the month and year similarly to the original data input.
i then use a macro to convert this inputted data on the filtering form to a date serial in the txtRangeBegin text box coded;
i am getting a data mismatch error. can anyone out there:
a) tell me if im going about this the right way, and;
b) help me to rectify this problem so as my form can filter my query error free
brenton savio
I would not store the dates as Months and Years, but would store the dates as actual dates and use the built-in functions in Access Month() and Year() to extract the month and year from the date.
but then the user has to enter the date initially as 1st of the approporiate month, an annoyance i was trying to avoid. i understand where you are coming from but i was trying to make the db more user friendly.
any other ideas?
i have been diagnosing the problem. it seems by biggest problem is converting the month and year to a date field within the query.
It the moment i have
PeriodRangeBegin: CDate(DateSerial([Admin3_PeriodRangeBeginYear],[Admin3_PeriodRangeBeginMonth],1)
and it is still either giving me a dta type mismatch error or a too complex field error.
can anyone help me convert this field to a date/time field rather than a text field...
does anyone know also, how to check what data types field within queries and forms are?
thanks in advance
I would still store the date as an actual date and use the DateSerial() function combine the month and year fields from the entry form.
Try this
still no luck
have the same error... :(
thanks for your help none the less although im well and truly stuck. will wait a bit and if no-one else has any ideas, i may have to restructure the way records are stored....
While I agree with truittb about storing the date as a date, let me ask a few pointed questions that may help us figure this out.
That is the format that you are storing the fields in the table? What are the datatypes? FieldNames?
Do you have controls on your search form? What are their names?
Let's start with the basics....
4 fields in the table tblFolders
dmin3_PeriodRangeBeginMonth - Number
Admin3_PeriodRangeBeginYear - Number
Admin3_PeriodRangeEndMonth - Number
Admin3_PeriodRangeEndYear - Number
both the controls are selected form drop down options in two other tables and they are limited to list
Othen have the search form
which contains
cboAdmin_3ai (drop down list of month options)
cboAdmin_3aii (drop down list of year options)
cboAdmin_3bi (drop down list of month options)
cboAdmin_3bii (drop down list of yr options)
these combo's have no control sources
and i have a command button on the form to load qrySearchFolders_Admin which has a number of criteria in which i cannot perfect.
archive is now 6mb but if you are interested, the current version can be downloaded from here:
www.users.on.net/~bsavio/Archive Database.mdb
again, any help is much appreciated
Compact and Repair the db, then zip it and then try to attach it. If it is still over 500,000 bytes then strip out unnecessary objects and try again.
here is the cut down version of the db
This is greatly appreciated
It is working for me. Which query are you having trouble with and which field in the query?
in the version i have attached, there is not criteria in the query to match the criteria in the form
if u open up frmsearchfolders and specify a range in which u want to search, it doesnt work as every bit of criteria i add to the query reuslts in an error
need to add suitable criteria to the last two field of the qrySearchFolders_admin query to match the form parameters in order to get it to work
sorry, i should have said that earlier
Ok, it is failing for me now. I don't have time to keep looking for a solution tonight. If I have time in the morning I'll see what I can do. In the meantime, read the following posts on Normalization. Once your database is normalized searching and reporting will be much easier. I would still strongly recommend storing the date as an actual date.
oah’s Normalization Link
Noah's Post
Jerry’s Treatise on Normalization
Jerry's Treatise on Normalization
Jerry’s Fully Qualified Naming
Fully Qualified Naming
Jerry’s Glossary
Jerry's Glossary
Danny's Reserved Words
Danny's Reserved Words

Naming Conventions
Hungarian Notation
no problems, thanks for your help so far.
look forward to hearing from you futher...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.