How to filter a "shortdate" in query from a "longdate" in subform

[neelsfer]

Use Access 2007;
"Admit" field in "SfAdmit" subform is a "long date" and the date format here is ddmmyyyy with time and seconds.

I created a short date unbound textbox in this same subform called "admit1" which is set to shortdate ddmmyyyy as i want to use this to filter a query.

Form + Subform + short date path from query  = [Forms]![frmAdmit]![sfAdmit]![admit1]

The "RxDate" field in the query is in "short date" format ddmmyyyy", where i add the above code in the criteria block.

Problem - it does not filter my query data based on the subform "admit1" field i use.
Any suggestions please?

[zmbd]

Form + Subform + short date path from query = [Forms]![frmAdmit]![sfAdmit]![admit1]


That first section (Form + Subform + short date path from query) isn't valid; thus, what are you actually using? (or maybe I'm being a bit obtuse - dealing with three littleones under 5 today (^_^) )

Also keep in mind if you use the "##" deliminators, Access expects the "#mm/dd/yyyy#" internally. Allen Browne: International Dates You may also need to use the BETWEEN to get the proper dates from ones with date and time. I tend to use a query to strip the time portion off and return just the date in a calculated field. Perhaps a bit of a kludge?!

Your subform reference appears wierd (Allen Browne: Referring to Controls on a Subform) try this version:
Forms.frmAdmit.sfAdmit.Form.admit1
some will argue with the dot/bang for that last so try this construct:
Me!Subform1.Form!ControlName
and if you are using the ACC2010-Navigation control use the bang.

THis is the reference I will use when I get lost:
Forms: Refer to Form and Subform properties and controls
I'm worried enough that this site might ever go down that I took advantage of the download offer of a hardcopy of the table - upper right hand corner!

[neelsfer]

Thx ZMBD
Apologies it is not long date but "general date" format that is causing issues.
This is what i am trying to explain in pictures.
Admit1 is derived by using =Admit in the control source in an unbound textbox, and setting the format to "Short date". (the Admit field is in "general date" format)

I would use "between" as soon as i get this to work and filter the query.

What i have noticed is if i try and filter this query from a different (short date) "date field" that also exists in this same subform, then it works great.

It only refuses to read the "Admit1"(Short date) field derived from the "Admit" field (general date) in the same subform.

Maybe i need to use a different way to create the short date from the general date on this same subform.

My reason for doing all of this is to create an invoice using a date range, based on the period the person was in a hospital and all medicines supplied in this period, between the admit and discharge dates.

Suggestions?

[zmbd]

Just for giggles insert ">=" before the form control in the query parameter (ie: >=[Forms]![tbl_datatype]![Text17].

If that doesn't change anything then change the ">" to "<" I've a suspicion that theres a time bug attached that you are not seeing... how is the format being set in [admit1]

If either of these work then something like:
DateValue([datefield_mmddyyyy])=DateValue([Forms]![tbl_datatype]![Text17])
Enter this in the same place you entered the other criteria (be aware of the fact that datevalue() works on the shortdate format of the PC at system level)

[neelsfer]

You are a star ZMBD, it works.

To use it with "admit1" and "discharge1" fields to see any transaction between the 2 dates, how would i incorporate the ">=" ?

Between [Forms]![frmAdmit]![sfAdmit]![admit1] and [Forms]![frmAdmit]![sfAdmit]![discharge1]

this does not work

>=[Forms]![frmAdmit]![sfAdmit]![admit1] And <=[Forms]![frmAdmit]![sfAdmit]![discharge1]

[zmbd]

The trouble comes from the fact that the date/time field always stores a time #01/25/2014 00h00# so the short date will show just #01/25/2014# so if the time stored is actually #01/25/2014 00h00:01# and you search on #01/25/2014# it wont return, etc...

So here's what I do when I don't care about times and I want something fairly easy without a lot of VBA.

Query, calculated field
zdatevalue: DateValue([datefield_mmddyyyy])
(caution with the datevalue, it returns a value that reflects the current system shortdate)

In the form I set two text boxes, with the format set to shortdate; thus, I know that the format returned should work with the calculated field.

Now in the criteria in the calculated field use your between
Between [Forms]![tbl_datatype]![Text17] And [Forms]![tbl_datatype]![Text22]

There are other methods with VBA and the like; however in the Query editor this is the easiest I've found. I am; however, always open to more and better methods.

[neelsfer]

Thx a million ZMBD. I have wasted 3 evenings, and with your help it is working now!