Append/Update Query

[Travis]

I have a table that contains the date in yyyymmdd format as text. I want to
convert the information to mm/dd/yyyy format as Date/Time. I created the
following expression to convert the yyyymmdd to mm/dd/yyyy format, however I
first have to copy the information into a new table and change the data type
to number before running the update query. What I would like to do is create
one query that will change the date from yyyymmdd to mm/dd/yyyy and append to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive from
a vendor. Once I get the file I copy/paste the information into the first
access table. Then run an update query to change the date from yyyymmdd to
mm/dd/yyyy. Finally I run an append query to add the daily records to the
final data table.

 

[John Spencer]

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Travis]

This is what I have to change the date:
Mid(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),5,2) & "/" &
Right(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),2) & "/" &
Left(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),4)

It works great, now I need a way to append the file.

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table that contains the date in yyyymmdd format as text. I want
to
convert the information to mm/dd/yyyy format as Date/Time. I created the
following expression to convert the yyyymmdd to mm/dd/yyyy format, however
I
first have to copy the information into a new table and change the data
type
to number before running the update query. What I would like to do is
create
one query that will change the date from yyyymmdd to mm/dd/yyyy and append
to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive
from
a vendor. Once I get the file I copy/paste the information into the first
access table. Then run an update query to change the date from yyyymmdd
to
mm/dd/yyyy. Finally I run an append query to add the daily records to the
final data table.

 

[John Spencer]

You can use the expression in the append query in place of the original
"Date" field.

You will have problems if the original field is every blank

You might consider posting your append query and telling us which field is
the original "date" field. I would think the expression I posted would be
faster and would also handle errors in the data better.

Actually I am surprised that your expression as posted works. DLookup
expects strings as its arguments, so I would expect to see something more
like
DLookup("De_state_entry_dt","CRExport","De_Account =""" & [De_Account] &
"""")
assuming that De_Account is a text field.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This is what I have to change the date:
Mid(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),5,2) & "/" &
Right(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),2) & "/" &
Left(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),4)

It works great, now I need a way to append the file.

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format
you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table that contains the date in yyyymmdd format as text. I
want
to
convert the information to mm/dd/yyyy format as Date/Time. I created
the
following expression to convert the yyyymmdd to mm/dd/yyyy format,
however
I
first have to copy the information into a new table and change the data
type
to number before running the update query. What I would like to do is
create
one query that will change the date from yyyymmdd to mm/dd/yyyy and
append
to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a
query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive
from
a vendor. Once I get the file I copy/paste the information into the
first
access table. Then run an update query to change the date from
yyyymmdd
to
mm/dd/yyyy. Finally I run an append query to add the daily records to
the
final data table.

 

[Travis]

This is the update query I currently have to change the date. The Data Type
for the date fields (Placement_Date and Trans_Date) are set to Text. So as I
past the data into the table it converts it to text. I'm assuming that's why
the Dlookup works.

If I use your suggestion below, do I just need to create an append query and
use your expression in the "Field:" column in place of the field name for the
table I'm appending to.

Thanks for you help. It is greatly appreciated.

UPDATE tbl_RAB_Remit_Import SET tbl_RAB_Remit_Import.Placement_Date =
Mid(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/"
& Right(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) &
"/" & Left(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),4),
tbl_RAB_Remit_Import.Trans_Date =
Mid(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/" &
Right(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) & "/" &
Left(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),4)

You can use the expression in the append query in place of the original
"Date" field.

You will have problems if the original field is every blank

You might consider posting your append query and telling us which field is
the original "date" field. I would think the expression I posted would be
faster and would also handle errors in the data better.

Actually I am surprised that your expression as posted works. DLookup
expects strings as its arguments, so I would expect to see something more
like
DLookup("De_state_entry_dt","CRExport","De_Account =""" & [De_Account] &
"""")
assuming that De_Account is a text field.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This is what I have to change the date:
Mid(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),5,2) & "/" &
Right(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),2) & "/" &
Left(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),4)

It works great, now I need a way to append the file.

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format
you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table that contains the date in yyyymmdd format as text. I
want
to
convert the information to mm/dd/yyyy format as Date/Time. I created
the
following expression to convert the yyyymmdd to mm/dd/yyyy format,
however
I
first have to copy the information into a new table and change the data
type
to number before running the update query. What I would like to do is
create
one query that will change the date from yyyymmdd to mm/dd/yyyy and
append
to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a
query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive
from
a vendor. Once I get the file I copy/paste the information into the
first
access table. Then run an update query to change the date from
yyyymmdd
to
mm/dd/yyyy. Finally I run an append query to add the daily records to
the
final data table.

 

[John Spencer]

AS I said before, using lookup the way you are should generate errors.
Your use does not follow the syntax for using the DLookup function.

If you are just trying to reformat the date into a text string, then I
would use the following to put the value into the TEXT field.



UPDATE tbl_RAB_Remit_Import
SET tbl_RAB_Remit_Import.Placement_Date =
Mid([Placement_Date],5,2) &
"/" & Right([Placement_Date],2) &
"/" & Left([Placement_Date],4)
, tbl_RAB_Remit_Import.Trans_Date =
Mid([Trans_Date],5,2) & "/" &
Right([Trans_Date],2) & "/" &
Left([Trans_Date],4)


What I would do would be to do this in the APPEND query. You have not
posted the APPEND query you are using.

Good Luck


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is the update query I currently have to change the date. The Data Type
for the date fields (Placement_Date and Trans_Date) are set to Text. So as I
past the data into the table it converts it to text. I'm assuming that's why
the Dlookup works.

If I use your suggestion below, do I just need to create an append query and
use your expression in the "Field:" column in place of the field name for the
table I'm appending to.

Thanks for you help. It is greatly appreciated.

UPDATE tbl_RAB_Remit_Import SET tbl_RAB_Remit_Import.Placement_Date =
Mid(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/"
& Right(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) &
"/" & Left(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),4),
tbl_RAB_Remit_Import.Trans_Date =
Mid(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/" &
Right(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) & "/" &
Left(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),4)

You can use the expression in the append query in place of the original
"Date" field.

You will have problems if the original field is every blank

You might consider posting your append query and telling us which field is
the original "date" field. I would think the expression I posted would be
faster and would also handle errors in the data better.

Actually I am surprised that your expression as posted works. DLookup
expects strings as its arguments, so I would expect to see something more
like
DLookup("De_state_entry_dt","CRExport","De_Account =""" & [De_Account] &
"""")
assuming that De_Account is a text field.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This is what I have to change the date:
Mid(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),5,2) & "/" &
Right(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),2) & "/" &
Left(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),4)

It works great, now I need a way to append the file.

:

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format
you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table that contains the date in yyyymmdd format as text. I
want
to
convert the information to mm/dd/yyyy format as Date/Time. I created
the
following expression to convert the yyyymmdd to mm/dd/yyyy format,
however
I
first have to copy the information into a new table and change the data
type
to number before running the update query. What I would like to do is
create
one query that will change the date from yyyymmdd to mm/dd/yyyy and
append
to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a
query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive
from
a vendor. Once I get the file I copy/paste the information into the
first
access table. Then run an update query to change the date from
yyyymmdd
to
mm/dd/yyyy. Finally I run an append query to add the daily records to
the
final data table.

 

[Travis]

Here is the append query:

INSERT INTO tbl_RAB_Remit ( Placement_Date, Customer_Name, Account_Num,
Trans_Date, Amt_Pd_Agency, Amt_Pd_Client, Amt_Due_Agency, Amt_Due_Client,
Amt_Commission, Starting_Balance, Remaining_Balance, Account_Status,
PROGRAM_TYPE )
SELECT tbl_RAB_Remit_Import.Placement_Date,
tbl_RAB_Remit_Import.Customer_Name, tbl_RAB_Remit_Import.Account_Num,
tbl_RAB_Remit_Import.Trans_Date, tbl_RAB_Remit_Import.Amt_Pd_Agency,
tbl_RAB_Remit_Import.Amt_Pd_Client, tbl_RAB_Remit_Import.Amt_Due_Agency,
tbl_RAB_Remit_Import.Amt_Due_Client, tbl_RAB_Remit_Import.Amt_Commission,
tbl_RAB_Remit_Import.Starting_Balance,
tbl_RAB_Remit_Import.Remaining_Balance, tbl_RAB_Remit_Import.Account_Status,
tbl_RAB_Remit_Import.PROGRAM_TYPE
FROM tbl_RAB_Remit_Import;

Both Placement_Date and Trans_Date are number format and appear as
########(20071215). My objective is to append tbl_RAB_Remit as date value in
this format MM/DD/YYYY.

AS I said before, using lookup the way you are should generate errors.
Your use does not follow the syntax for using the DLookup function.

If you are just trying to reformat the date into a text string, then I
would use the following to put the value into the TEXT field.



UPDATE tbl_RAB_Remit_Import
SET tbl_RAB_Remit_Import.Placement_Date =
Mid([Placement_Date],5,2) &
"/" & Right([Placement_Date],2) &
"/" & Left([Placement_Date],4)
, tbl_RAB_Remit_Import.Trans_Date =
Mid([Trans_Date],5,2) & "/" &
Right([Trans_Date],2) & "/" &
Left([Trans_Date],4)


What I would do would be to do this in the APPEND query. You have not
posted the APPEND query you are using.

Good Luck


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

This is the update query I currently have to change the date. The Data Type
for the date fields (Placement_Date and Trans_Date) are set to Text. So as I
past the data into the table it converts it to text. I'm assuming that's why
the Dlookup works.

If I use your suggestion below, do I just need to create an append query and
use your expression in the "Field:" column in place of the field name for the
table I'm appending to.

Thanks for you help. It is greatly appreciated.

UPDATE tbl_RAB_Remit_Import SET tbl_RAB_Remit_Import.Placement_Date =
Mid(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/"
& Right(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) &
"/" & Left(DLookUp([Placement_Date],"tbl_RAB_Remit_Import",[Account_Num]),4),
tbl_RAB_Remit_Import.Trans_Date =
Mid(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),5,2) & "/" &
Right(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),2) & "/" &
Left(DLookUp([Trans_Date],"tbl_RAB_Remit_Import",[Account_Num]),4)

You can use the expression in the append query in place of the original
"Date" field.

You will have problems if the original field is every blank

You might consider posting your append query and telling us which field is
the original "date" field. I would think the expression I posted would be
faster and would also handle errors in the data better.

Actually I am surprised that your expression as posted works. DLookup
expects strings as its arguments, so I would expect to see something more
like
DLookup("De_state_entry_dt","CRExport","De_Account =""" & [De_Account] &
"""")
assuming that De_Account is a text field.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

This is what I have to change the date:
Mid(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),5,2) & "/" &
Right(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),2) & "/" &
Left(DLookUp([DE_state_entry_dt],"CREXPORT",[DE_Account]),4)

It works great, now I need a way to append the file.

:

You should be able to use the following to return a date. Once you have
stored the date in a date time field, you can apply any DISPLAY format
you
wish.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],"@@@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a table that contains the date in yyyymmdd format as text. I
want
to
convert the information to mm/dd/yyyy format as Date/Time. I created
the
following expression to convert the yyyymmdd to mm/dd/yyyy format,
however
I
first have to copy the information into a new table and change the data
type
to number before running the update query. What I would like to do is
create
one query that will change the date from yyyymmdd to mm/dd/yyyy and
append
to
a table where the daily records will accumulate.

Is there a way to make all this happen in one step? Can I create a
query
that appends to a table and updates the data?

Background information: The data originates in an excel file I receive
from
a vendor. Once I get the file I copy/paste the information into the
first
access table. Then run an update query to change the date from
yyyymmdd
to
mm/dd/yyyy. Finally I run an append query to add the daily records to
the
final data table.