Thread: psql dates in Access
in postgres 7.2
Using the postgres timestamp with timezone, I can't seem to extract records using Access and requesting a range of dates, unless the date is hard-coded into the Access query.
I want to reference a range of dates that a user has entered on a form.
I have referenced to user-entered dates with and without the date bracketed by "#".
In the date range I submit to the query I have referenced the full time stamp string where I construct the start date as "xx/xx/xx 12:00:00 AM" and the end date as "xx/xx/xx 12:59:59 PM" (not the same xx/xx/xx obvioulsy)
I have referenced the dates with and without the double quotes.
I have referenced only the left 10 characters of the dates, i.e., the day part.
When I manually enter the desired dates into the query before saving, then running, it works.
When I try any of the above ways to reference a date from a form, the query results are always empty.
Any ideas?
Thanks
Fred Parkinson
Try without time zone; that should work. John. On Sunday 23 March 2003 02:51 pm, Fred Parkinson wrote: > in postgres 7.2 > Using the postgres timestamp with timezone, I can't seem to extract records > using Access and requesting a range of dates, unless the date is hard-coded > into the Access query. > > I want to reference a range of dates that a user has entered on a form. > I have referenced to user-entered dates with and without the date bracketed > by "#". In the date range I submit to the query I have referenced the full > time stamp string where I construct the start date as "xx/xx/xx 12:00:00 > AM" and the end date as "xx/xx/xx 12:59:59 PM" (not the same xx/xx/xx > obvioulsy) I have referenced the dates with and without the double quotes. > I have referenced only the left 10 characters of the dates, i.e., the day > part. > > When I manually enter the desired dates into the query before saving, then > running, it works. When I try any of the above ways to reference a date > from a form, the query results are always empty. > > Any ideas? > Thanks > Fred Parkinson
I will look into that, but I seem to recall that when we were setting up the databases in postgres a couple of months ago, that if we left the time zone off then access didn't recognize the field as a date/time field
Thanks for responding
Fred
>>> "John K. Herreshoff" <jkherr@centurytel.net> 03/23/03 12:44PM >>>
Try without time zone; that should work.
John.
On Sunday 23 March 2003 02:51 pm, Fred Parkinson wrote:
> in postgres 7.2
> Using the postgres timestamp with timezone, I can't seem to extract records
> using Access and requesting a range of dates, unless the date is hard-coded
> into the Access query.
>
> I want to reference a range of dates that a user has entered on a form.
> I have referenced to user-entered dates with and without the date bracketed
> by "#". In the date range I submit to the query I have referenced the full
> time stamp string where I construct the start date as "xx/xx/xx 12:00:00
> AM" and the end date as "xx/xx/xx 12:59:59 PM" (not the same xx/xx/xx
> obvioulsy) I have referenced the dates with and without the double quotes.
> I have referenced only the left 10 characters of the dates, i.e., the day
> part.
>
> When I manually enter the desired dates into the query before saving, then
> running, it works. When I try any of the above ways to reference a date
> from a form, the query results are always empty.
>
> Any ideas?
> Thanks
> Fred Parkinson
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
"Fred Parkinson" <FredP@abag.ca.gov> writes:
> When I manually enter the desired dates into the query before saving,=20
> then<BR>> running, it works. When I try any of the above ways to referen=
> ce a=20
> date<BR>> from a form, the query results are always empty.<BR>><BR>&g=
> t;=20
[please turn off MIME encoding in list mail...]
It seems likely to me that the query arriving at the backend is not what
you think it is. I'd suggest turning on query logging (see
postgresql.conf) so that you can see what's really being issued. IIRC,
there are also logging provisions in the ODBC driver, which would give
you an additional view of what's happening.
regards, tom lane