Append Query runs without error, but no output

  • Thread starter Thread starter Tony Girgenti
  • Start date Start date
T

Tony Girgenti

Hello.

WIN XP Pro sp2, Access 2003.

I developed an append query which has a criteria on one of the fields. The
query runs without error, but no records are put into the new database.

The criteria field is on a text field, but we are storing a Date/Time in the
text field ie."3/01/2005 12:55:23 PM". I am comparing the text field to a
form field with a format of Short Date. I stopped the debugger after the
date is entered on the form and it shows the form field date as
"12/07/2005".

I think that is my problem. So how do i make the two date fields the same
format for comparison ?

Thanks,
Tony
 
Backup your data first.

Use DateValue function to force your text string to a date. DateValue will
return only the date porition.

Field: JustADate: DateValue(YourTextDateField)
Criteria: [Forms]![YourFormName]![YourControlName]
 
Hi John.

This still does not work. Just to make sure i did the right thing, my table
text field is called TIMESTAMP. I put on the Field: line for the TIMESTAMP
column "DateValue([TIMESTAMP])" and my Criteria: line says
"<[Forms]![Form1]![Text1]".

After closing the query and going back to look at it, Access put "Expr1:" in
front of the table field expression.

Thanks again.
Tony

John Spencer said:
Backup your data first.

Use DateValue function to force your text string to a date. DateValue
will return only the date porition.

Field: JustADate: DateValue(YourTextDateField)
Criteria: [Forms]![YourFormName]![YourControlName]


Tony Girgenti said:
Hello.

WIN XP Pro sp2, Access 2003.

I developed an append query which has a criteria on one of the fields.
The query runs without error, but no records are put into the new
database.

The criteria field is on a text field, but we are storing a Date/Time in
the text field ie."3/01/2005 12:55:23 PM". I am comparing the text field
to a form field with a format of Short Date. I stopped the debugger
after the date is entered on the form and it shows the form field date as
"12/07/2005".

I think that is my problem. So how do i make the two date fields the
same format for comparison ?

Thanks,
Tony
Click to expand...
Click to expand...
 
Basic question, that I should have asked.

When you RUN the query are you simply switching views? If so, the query
APPEND action does not take place. In design view, click on the red
exclamation mark or select Query: Run from the menu.

The "Expr1:" is simply a name (an alias) that Access assigns to the
calculated column as all columns must have a name. You can change that to
anything you wish. For instance,
Field: RealDate: DateValue([TimeStamp])

Normally when you run an action query you will get a dialog box from Access
saying it is about to append (delete, change) some number of records. If
you are running this query from the database window and don't see this
message then either you are not running the query or you have turned off
this feature.

Tony Girgenti said:
Hi John.

This still does not work. Just to make sure i did the right thing, my
table text field is called TIMESTAMP. I put on the Field: line for the
TIMESTAMP column "DateValue([TIMESTAMP])" and my Criteria: line says
"<[Forms]![Form1]![Text1]".

After closing the query and going back to look at it, Access put "Expr1:"
in front of the table field expression.

Thanks again.
Tony

John Spencer said:
Backup your data first.

Use DateValue function to force your text string to a date. DateValue
will return only the date porition.

Field: JustADate: DateValue(YourTextDateField)
Criteria: [Forms]![YourFormName]![YourControlName]


Tony Girgenti said:
Hello.

WIN XP Pro sp2, Access 2003.

I developed an append query which has a criteria on one of the fields.
The query runs without error, but no records are put into the new
database.

The criteria field is on a text field, but we are storing a Date/Time in
the text field ie."3/01/2005 12:55:23 PM". I am comparing the text
field to a form field with a format of Short Date. I stopped the
debugger after the date is entered on the form and it shows the form
field date as "12/07/2005".

I think that is my problem. So how do i make the two date fields the
same format for comparison ?

Thanks,
Tony
Click to expand...
Click to expand...
Click to expand...
 
Hi John.

I'm using this in the command button click procedure "DoCmd.OpenQuery "ArchiveAppend"".

When it runs, i get the following screens.



John Spencer said:
Basic question, that I should have asked.

When you RUN the query are you simply switching views? If so, the query
APPEND action does not take place. In design view, click on the red
exclamation mark or select Query: Run from the menu.

The "Expr1:" is simply a name (an alias) that Access assigns to the
calculated column as all columns must have a name. You can change that to
anything you wish. For instance,
Field: RealDate: DateValue([TimeStamp])

Normally when you run an action query you will get a dialog box from Access
saying it is about to append (delete, change) some number of records. If
you are running this query from the database window and don't see this
message then either you are not running the query or you have turned off
this feature.

Tony Girgenti said:
Hi John.

This still does not work. Just to make sure i did the right thing, my
table text field is called TIMESTAMP. I put on the Field: line for the
TIMESTAMP column "DateValue([TIMESTAMP])" and my Criteria: line says
"<[Forms]![Form1]![Text1]".

After closing the query and going back to look at it, Access put "Expr1:"
in front of the table field expression.

Thanks again.
Tony

John Spencer said:
Backup your data first.

Use DateValue function to force your text string to a date. DateValue
will return only the date porition.

Field: JustADate: DateValue(YourTextDateField)
Criteria: [Forms]![YourFormName]![YourControlName]


Hello.

WIN XP Pro sp2, Access 2003.

I developed an append query which has a criteria on one of the fields.
The query runs without error, but no records are put into the new
database.

The criteria field is on a text field, but we are storing a Date/Time in
the text field ie."3/01/2005 12:55:23 PM". I am comparing the text
field to a form field with a format of Short Date. I stopped the
debugger after the date is entered on the form and it shows the form
field date as "12/07/2005".

I think that is my problem. So how do i make the two date fields the
same format for comparison ?

Thanks,
Tony
Click to expand...
Click to expand...
Click to expand...
 
Tony,
Please TYPE the messages you are getting. I automatically screen out anything that is not text.

Also, copy the SQL text of the query and post that.
Also, copy the vba code you are trying to run.

John
Hi John.

I'm using this in the command button click procedure "DoCmd.OpenQuery "ArchiveAppend"".

When it runs, i get the following screens.
 
Hello John.

There should be a better way to put messages received into a newsgroup message.

Here is my click procedure==============================================
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

DoCmd.OpenQuery "ArchiveAppend"
DoCmd.OpenQuery "ArchiveDelete"

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

Here is the sql verbage from the append query=============================

INSERT INTO [PENDING ORDERS] ( [COMPANY NAME], SIDEMARK, [TIMESTAMP], WORKSTATION, JOBNO, DESCRIPTION, TARGET, [NUMBER OF FABRICS] ) IN 'C:\Documents and Settings\Administrator\My Documents\Utility\CHF\WIP System\Copy of CHF DATA.MDB'
SELECT [PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING ORDERS].TIMESTAMP, [PENDING ORDERS].WORKSTATION, [PENDING ORDERS].JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET, [PENDING ORDERS].[NUMBER OF FABRICS]
FROM [PENDING ORDERS] WHERE (((DateValue([TIMESTAMP]))<[Forms]![Form1]![Text1]));

First Message screen received=============================================

You are about to run an append query that will modify data in your table.

Are you sure you want to run this type of action query?
For information on turning off confirmation messages for document deleteions, click help.


Second Message screen received=============================================

You are about to append 0 row(s).

Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?

Tony,
Please TYPE the messages you are getting. I automatically screen out anything that is not text.

Also, copy the SQL text of the query and post that.
Also, copy the vba code you are trying to run.

John
Hi John.

I'm using this in the command button click procedure "DoCmd.OpenQuery "ArchiveAppend"".

When it runs, i get the following screens.
 
Well everything looks good. So I am stuck.

Are you sure that TIMESTAMP is a date time field. In MS SQL a TimeStamp field is not a datetime field. It is a value that uniquely identifies a row and the version of that row's data. It changes when a row is changed.

So the only thing I can think of right now is that the TimeStamp is not a date time field.

The other possibility is that the accuracy of the DateTime is a bit off when it is being calculated and you will need a range of times.

AHHH! One other possibility is that Forms!Form1!Text1 is not being recognized as a date. Try changing the where to

WHERE DateValue([TimeStamp])< CDate([Forms]![Form1]![Text1])

WHERE DateValue([TIMESTAMP])<[Forms]![Form1]![Text1]
Hello John.

There should be a better way to put messages received into a newsgroup message.

Here is my click procedure==============================================
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

DoCmd.OpenQuery "ArchiveAppend"
DoCmd.OpenQuery "ArchiveDelete"

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

Here is the sql verbage from the append query=============================

INSERT INTO [PENDING ORDERS] ( [COMPANY NAME], SIDEMARK, [TIMESTAMP], WORKSTATION, JOBNO, DESCRIPTION, TARGET, [NUMBER OF FABRICS] ) IN 'C:\Documents and Settings\Administrator\My Documents\Utility\CHF\WIP System\Copy of CHF DATA.MDB'
SELECT [PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING ORDERS].TIMESTAMP, [PENDING ORDERS].WORKSTATION, [PENDING ORDERS].JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET, [PENDING ORDERS].[NUMBER OF FABRICS]
FROM [PENDING ORDERS] WHERE (((DateValue([TIMESTAMP]))<[Forms]![Form1]![Text1]));

First Message screen received=============================================

You are about to run an append query that will modify data in your table.

Are you sure you want to run this type of action query?
For information on turning off confirmation messages for document deleteions, click help.


Second Message screen received=============================================

You are about to append 0 row(s).

Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?

Tony,
Please TYPE the messages you are getting. I automatically screen out anything that is not text.

Also, copy the SQL text of the query and post that.
Also, copy the vba code you are trying to run.

John
Hi John.

I'm using this in the command button click procedure "DoCmd.OpenQuery "ArchiveAppend"".

When it runs, i get the following screens.
 
Back
Top