Display Date from Unbound Field in Form in a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Just had an issue that in 10 years of using Access never saw - just wondering
if I just never encountered it or what...

I have a form running a query, the user enters a date. The report needs to
show the date - so the query has a field like this:

report date: [Forms!]![frm_report]![txtReportDate]

I had a problem exporting to Excel - seems the direct placement in the query
led to characters that Excel could not understand. I actually had another
date field from the form that I did a date add on, it worked fine. I
eventaully put the form reference in a Format, the issue was gone.

The question for is why have I never seen this before? I can't believe that
I've been so precise over the years to always format a date that I pulled
from a form into a query, and I'm sure that I've done this before (show a
date from a form in a query).

Any comments would be appreciated. I guess we do learn something everyday...
 
Ed,

I've run into a number of issues WRT using Excel data queries of Access
databases. Cannot say I've ever run into this one though. Are you using
automation to open Excel, or are you doing it manually and refreshing an
Excel data query to the query in your Access database. What surprises me is
that it evaluated the Format( ) function before bringing your data over.

I have a number of applications where I create user defined functions to
wrap complicated logic into a query. Unfortunately, Excel (at least 2003 and
earlier) will not evaluate the Access UDF if you try to use the Excel data
query.

Dale
 
Hi Dale - I think I probably didn't explain the situation well. :-)

I'm opening the query in Access via a form where the user enters the dates.
I'm displaying the dates in the query - but one of the dates gets "23:59:59"
appended to it. I do that by first adding 1 to the values of the text box
that has the date, then using the dateadd funtion to subtract a second. That
date displayed fine. The start date did not display when I just had the
reference to the form's text box in the query field. When I changed the
query expression to something that did somethign trivial to the value from
the form (formatted a short date as a short date or used dateadd to add 0
seconds) the display in Access was fine.

I give my users (when I can) the option to export the query data to Excel
using the TransferSpreadsheet action of the DoCmd object - before I tweaked
the query expression for the start date I got an error when Access was
attempting to do the transfer to Excel. I *think* Excel was complaining
about the value Access was trying to feed it, but the "problem" appears to be
from Access.

It just seems to me that I *have to have* done this before - display a date
that was entered in a text box of a form in a query, and I doubt that I've
*always* used the format funtion to do it. But maybe I've always do that and
just never realized it...I've worked with quite a few development tools over
the years so I might just be confused trying to remember things I've done in
the past. :-) The thing I'm wondering about is can I do this (where the
forms reference is a text box formatted/input masked for a short date) and
have it display correctly:

select *, [Forms]![frm_test]![txtDate] As "Form Date" from tblStuff

Thanks for the reply!
 
Back
Top