Make Table - how to save input from a prompt

[billbarl]

I am converting from 97 to 2000. I am having trouble with a make table query that contains a prompt (for start date and end date) and puts the data in a new table. It works fine in 97, but in 2000 it returns an error that states "... can't add all the records in the update or append query. ... It set 676 fields to Null due to a type conversion failure."

The table does not exist, so Access can't be looking at the table specs. If I change the query to a select query, it displays all the data correctly. But as soon as I change it to a Make Table query, I get the error.

Within the table it creates, all the other data from the table I am retrieving from is there, but the start and end date columns are blank.

 

[tlbroadbent]

It would be helpful to see your query! Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[billbarl]

The SQL code that's generated is:

SELECT DISTINCT [startdate] AS StartDateTest, [endate] AS EndDateTest, EMPAC_EWM_WO_AUDITS.DATE_TIME INTO tblTestDates
FROM EMPAC_EWM_WO_AUDITS
WHERE (((EMPAC_EWM_WO_AUDITS.DATE_TIME) Between [startdate] And [endate]));

 

[tlbroadbent]

Use the CDate Function to convert the Date columns. I also recommend using IIf and IsNull functions to handle nulls.

SELECT DISTINCT
IIf(IsNull([StartDate]),Null,CDate([StartDate])) AS StartDateTest,
IIf(IsNull([EndDate]),Null,CDate([EndDate])) AS EndDateTest,
EMPAC_EWM_WO_AUDITS.DATE_TIME INTO tblTestDates
FROM EMPAC_EWM_WO_AUDITS
WHERE EMPAC_EWM_WO_AUDITS.DATE_TIME Between
IIf(IsNull([StartDate]),Null,CDate([StartDate]))
And IIf(IsNull([EndDate]),Null,CDate([EndDate]));

Review the result as a SELECT query to see if nulls appear anywhere in the output. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[billbarl]

Tried your code, now I get "ODBC - data out of range" if I run it as a select query, and "[Microsoft][ODBC driver for Oracle]Error in column1atetime field overflow (#0)" if I run it as a make table query.

When I run my original code, it selects the records from the database table, and puts the data into my new Access table - it just won't put the dates I prompt for into the Access table. It sets only those columns to null - none of the data from the data base is set to null.

 

[tlbroadbent]

What date format are you entering when prompted? Type of column is on the linked Oracle database. Is it a datetime column of a text (char) column? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[billbarl]

It's a date/time column. But - I rewrote the darn thing to just use the form in subsequent queries & the report, instead of trying to load the dates in a table. The rewrite works fine.

Thanks for your help on this, it's really great to be able to ask questions and get solid answers. I appreciate your efforts.