ODBC with AS400

[Ryan]

I have an ODBC connection with an AS/400. Everything with works great except
when I add a date parameter. DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows
the date with the formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End Date], it
shows all days and years. What I mean is that it only looks like its
filtering on the month so if my parameters are 07/01/2008 and 07/15/2008, it
will show records with 07/21/2008 and 07/01/2006 and so on. Anyone know why
it would show other days and years than whats specified in the where clause?
--

 

[Ryan]

I forgot to mention that the dates from the AS/400 come in looking like this
2008-07-01. In the linked table access shows this column as a text field,
but according to the AS/400 documentation this is a number field.

 

[Bob Barrows [MVP]]

In order to use BETWEEN, you have to convert the data in TRTDAT to dates
instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

 

[Ryan]

I have used CDate([TRTDAT]), but since there are 1.8 million records, it
takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your problem.

In order to use BETWEEN, you have to convert the data in TRTDAT to dates
instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Do it ahead of time: create a new table containing the primary key
fields from the linked table and a new date/time field (indexed). Run an
update query to update the new date/time field with the value from the
matching record in the linked table.
Then join this table to the linked table and filter using the date/time
field.

I have used CDate([TRTDAT]), but since there are 1.8 million records,
it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.

In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --


:

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Ryan]

Two quick questions about update queries. The first time I run an update
query it is going to return every record in the Update To table, and the next
time I run the query it will update each field that has changed and add any
new records that have been added, correct?
--
Please remember to mark this post as answered if this solves your problem.

Do it ahead of time: create a new table containing the primary key
fields from the linked table and a new date/time field (indexed). Run an
update query to update the new date/time field with the value from the
matching record in the linked table.
Then join this table to the linked table and filter using the date/time
field.

I have used CDate([TRTDAT]), but since there are 1.8 million records,
it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.

In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

Ryan wrote:
I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --


:

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Only if you write it that way.
I misspoke in my original post. I should have said that you would
append all the new records from the linked table into the local table:

Insert into localtable
select primarykeyfields, CDate(TRTDAT) from linkedtable

Subsequent refreshes can use an Upsert query which is only possible in
Jet AFAIK. Something like this:
Update localtable h right join linkedtable a
on h.primarykeyfields = a.primarykeyfields
set h.TRTDAT_date = CDate(a.TRTDAT)
WHERE h.TRTDAT_date <> CDate(a.TRTDAT)

Two quick questions about update queries. The first time I run an
update query it is going to return every record in the Update To
table, and the next time I run the query it will update each field
that has changed and add any new records that have been added,
correct? --
Please remember to mark this post as answered if this solves your
problem.

Do it ahead of time: create a new table containing the primary key
fields from the linked table and a new date/time field (indexed).
Run an update query to update the new date/time field with the value
from the matching record in the linked table.
Then join this table to the linked table and filter using the
date/time field.

I have used CDate([TRTDAT]), but since there are 1.8 million
records, it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.


:

In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

Ryan wrote:
I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this
column as a text field, but according to the AS/400 documentation
this is a number field. --


:

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008
and 07/01/2006 and so on. Anyone know why it would show other
days and years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Wait a minute!!!
An alternative would be to guarantee that your criteria format matches
the original TRTDAT format:

Try this:
Between
WHERE TRTDAT Between
Format([Forms]![Run Report]![StartDate],"yyyy-mm-dd") And
Format([Forms]![Run Report]![End Date],"yyyy-mm-dd")

I have used CDate([TRTDAT]), but since there are 1.8 million records,
it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.

In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --


:

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Ryan]

Brilliant. Query took like 18 seconds. Then I ran another query on top of
that and used CDate() to convert it to a date/time. Thanks for sticking with
me on this one.
--
Please remember to mark this post as answered if this solves your problem.

Wait a minute!!!
An alternative would be to guarantee that your criteria format matches
the original TRTDAT format:

Try this:
Between
WHERE TRTDAT Between
Format([Forms]![Run Report]![StartDate],"yyyy-mm-dd") And
Format([Forms]![Run Report]![End Date],"yyyy-mm-dd")

I have used CDate([TRTDAT]), but since there are 1.8 million records,
it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.

In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:

DateFilter: CDate([TRTDAT])

should work.

Ryan wrote:
I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this column
as a text field, but according to the AS/400 documentation this is a
number field. --


:

I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008 and
07/01/2006 and so on. Anyone know why it would show other days and
years than whats specified in the where clause? --

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.