How to automate this...?

B

Bonnie

Hello everyone! Using A02 on XP. Not a programmer but lovin' learnin'!

I have a little DB that I'm using to import .txt file from our system,
replace SSN's with "000000000" and add an AutoNum ID field (for privacy
issues) then spit out .xls file to a client for editing, we then import the
returned edited file, and then I need to reinsert the SSN's and export the
edited data to a .csv file to be loaded into our system. I've learned how to
use macros to import the file (named GPXXXX.txt) in a special folder that is
named the same as the contract number field on my form plus add the text
"Copy" or "Revised" to the table name when it is imported. So I'll end up
with 2 tables in my DB, one is GP1992Copy (pre-edit) and the other
GP1992Revised (post-edit).

First, my compare query (see below) needs to check the SSN fields and IF SSN
= "000000000" use GP1992Copy.SSN ELSE use GP1992Revised.SSN. So all SSN's
should end up in a field called [Social]. All records sent SSN field will
equal "000000000" but if the client adds new records, the SSN field will
contain a real SSN and I need that. So this will combine the 2 fields to one
IF they have a real SSN. I used the ID field to link. I've created an SQL
that will do it if I name the tables. I want to insert the variable wordage
that I have added below the SQL.

SELECT GP1992Revised.ID, GP1992Copy.SSN, GP1992Revised.SSN,
IIf(([GP1992Revised]![SSN])="000000000",[GP1992Copy]![SSN],[GP1992Revised]![SSN])
AS Social, GP1992Revised.[First Name], GP1992Revised.[Last Name],
GP1992Revised.Gender, GP1992Revised.[Date of Birth], GP1992Revised.[Date of
Hire], GP1992Revised.Hours, GP1992Revised.Compensation,
GP1992Revised.[Deferral Amount], GP1992Revised.[Excludable Compensation],
GP1992Revised.[Section 125], GP1992Revised.[Status Code],
GP1992Revised.[Status Date]
FROM GP1992Copy RIGHT JOIN GP1992Revised ON GP1992Copy.ID = GP1992Revised.ID
WITH OWNERACCESS OPTION;

These are the two tables that should be used if I can get it to work.

[Forms]![fCensus1Conversion]![RunThisOne] & "Copy" (ex. GP1992Copy)

[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" (ex. GP1992Revised)

I replaced the items with this:

SELECT [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID,
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".SSN,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".SSN,
IIf(([Forms]![fCensus1Conversion]![RunThisOne] &
"Revised"![SSN])="000000000",[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"![SSN],[Forms]![fCensus1Conversion]![RunThisOne] & "Revised"![SSN]) AS
Social, [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[First Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Last Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Gender,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Birth],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Hire],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Hours,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Compensation,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Deferral Amount],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Excludable
Compensation], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Section
125], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Code],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Date]
FROM [Forms]![fCensus1Conversion]![RunThisOne] & "Copy" RIGHT JOIN
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" ON
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".ID =
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID
WITH OWNERACCESS OPTION;

But when I run it I get an error: Syntax Error in From Clause

I'm sure it's something simple. I would really appreciate any help or
advice. Thanks in advance for your time.
 
S

SteveS

Bonnie said:
Hello everyone! Using A02 on XP. Not a programmer but lovin' learnin'!

I have a little DB that I'm using to import .txt file from our system,
replace SSN's with "000000000" and add an AutoNum ID field (for privacy
issues) then spit out .xls file to a client for editing, we then import the
returned edited file, and then I need to reinsert the SSN's and export the
edited data to a .csv file to be loaded into our system. I've learned how to
use macros to import the file (named GPXXXX.txt) in a special folder that is
named the same as the contract number field on my form plus add the text
"Copy" or "Revised" to the table name when it is imported. So I'll end up
with 2 tables in my DB, one is GP1992Copy (pre-edit) and the other
GP1992Revised (post-edit).

First, my compare query (see below) needs to check the SSN fields and IF SSN
= "000000000" use GP1992Copy.SSN ELSE use GP1992Revised.SSN. So all SSN's
should end up in a field called [Social]. All records sent SSN field will
equal "000000000" but if the client adds new records, the SSN field will
contain a real SSN and I need that. So this will combine the 2 fields to one
IF they have a real SSN. I used the ID field to link. I've created an SQL
that will do it if I name the tables. I want to insert the variable wordage
that I have added below the SQL.

SELECT GP1992Revised.ID, GP1992Copy.SSN, GP1992Revised.SSN,
IIf(([GP1992Revised]![SSN])="000000000",[GP1992Copy]![SSN],[GP1992Revised]![SSN])
AS Social, GP1992Revised.[First Name], GP1992Revised.[Last Name],
GP1992Revised.Gender, GP1992Revised.[Date of Birth], GP1992Revised.[Date of
Hire], GP1992Revised.Hours, GP1992Revised.Compensation,
GP1992Revised.[Deferral Amount], GP1992Revised.[Excludable Compensation],
GP1992Revised.[Section 125], GP1992Revised.[Status Code],
GP1992Revised.[Status Date]
FROM GP1992Copy RIGHT JOIN GP1992Revised ON GP1992Copy.ID = GP1992Revised.ID
WITH OWNERACCESS OPTION;

These are the two tables that should be used if I can get it to work.

[Forms]![fCensus1Conversion]![RunThisOne] & "Copy" (ex. GP1992Copy)

[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" (ex. GP1992Revised)

I replaced the items with this:

SELECT [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID,
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".SSN,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".SSN,
IIf(([Forms]![fCensus1Conversion]![RunThisOne] &
"Revised"![SSN])="000000000",[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"![SSN],[Forms]![fCensus1Conversion]![RunThisOne] & "Revised"![SSN]) AS
Social, [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[First Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Last Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Gender,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Birth],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Hire],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Hours,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Compensation,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Deferral Amount],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Excludable
Compensation], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Section
125], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Code],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Date]
FROM [Forms]![fCensus1Conversion]![RunThisOne] & "Copy" RIGHT JOIN
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" ON
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".ID =
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID
WITH OWNERACCESS OPTION;

But when I run it I get an error: Syntax Error in From Clause

I'm sure it's something simple. I would really appreciate any help or
advice. Thanks in advance for your time.
Click to expand...

Bonnie,

I don't think the query can resolve a reference to a control on a form to
create a table name. You could use VB to create a string of the correct SQL,
then assign the string as the row source.

If the query is a saved query, you could modify the SQL of the query, then
open the form/report.

As a test, create a button on form "fCensus1Conversion".
Paste in the following code: (watch for line wrap!)

'--------------------------------
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.SSN, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.SSN, "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN])= '000000000', "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy![SSN], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN]) AS Social, "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[First Name], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Last
Name], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Gender, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Birth], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Hire], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Hours, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Compensation, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Deferral Amount], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Excludable Compensation], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Section 125], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Code], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date] "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy RIGHT
JOIN "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised ON "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.ID = "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'change "YourQuery" to the name of your query
Set qryTest = dbsCurrent.QueryDefs("YourQuery")
qryTest.SQL = strSQL

'other code

Set dbsCurrent = Nothing
Set qryTest = Nothing
'--------------------------------

Uncomment the MsgBox line or add a Debug.Pring strSQL line.

It should give you the correct SQL for the value selected in the
"RunThisOne" combo box.


HTH
 
B

Bonnie

SteveS, you are not only a genius, but a sharing genius. I could NEVER have
written the code you gave to me. AND, it works!!! But, of course, there are
the inevitable trailer questions.

I have 3 date fields in my export and need to format them to be dates only
rather than date/time. (Need to export 01/01/1945 rather than 01/01/1945
0:00:00.) How would I alter the lines? Here is one (I can do the rest). Or
is it done at the top of the function?

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date] "

I am SO amazed at how it went into an existing query and changed even the
base tables it was pulling from. I absolutely LOVE Access and am trying to
learn as much as I can. The VB part is mind-blowing.

I added a line at the bottom to run a transfertext macro but I'm getting an
error on naming the file. For some reason Access is saying "Run Time Error
3011" "The Microsoft Jet database engine can't find the object 'GP0013.csv'.
Make sure the object exists and that you spell it's name and file path name
correctly." Well, GP0013.csv is what the exported data file should be named
but it seems to think it should already be there like it's looking to get
something FROM it.

I have this in my Action Arguments of the TransferText macro
mData.Census1Done:

Transfer Type: Export Delimited
Specification Name: CensusExportSpec1
Table Name: qExportRevisedCensus1
File Name: ="S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv"
Has Field Names: No

The Table Name is the name of the 'thing' I am importing to or exporting
from so it should be the query that I want to run in Access to export my data
out to a comma delimited text file.

The File Name is the full path of the text file that I am importing from or
exporting to. (I have even tried keying just "GP0013.txt" into this line and
STILL get the same error.)

It seems that Access has it backwards and is saying it can't find the file I
want to IMPORT. Can you see if I've done something juvenile that I can't see
for the trees?

I'm SO close!!! Just need the date formating and be able to export the
file. If you have VB that will do it better than the macro, please feel free
to share it with me. I promise to use it and send positive thought waves out
into the world about you and your generosity.

Thanks again for your help and thanks in advance for any additional
assistance!
--
Bonnie


SteveS said:
Bonnie said:
Hello everyone! Using A02 on XP. Not a programmer but lovin' learnin'!

I have a little DB that I'm using to import .txt file from our system,
replace SSN's with "000000000" and add an AutoNum ID field (for privacy
issues) then spit out .xls file to a client for editing, we then import the
returned edited file, and then I need to reinsert the SSN's and export the
edited data to a .csv file to be loaded into our system. I've learned how to
use macros to import the file (named GPXXXX.txt) in a special folder that is
named the same as the contract number field on my form plus add the text
"Copy" or "Revised" to the table name when it is imported. So I'll end up
with 2 tables in my DB, one is GP1992Copy (pre-edit) and the other
GP1992Revised (post-edit).

First, my compare query (see below) needs to check the SSN fields and IF SSN
= "000000000" use GP1992Copy.SSN ELSE use GP1992Revised.SSN. So all SSN's
should end up in a field called [Social]. All records sent SSN field will
equal "000000000" but if the client adds new records, the SSN field will
contain a real SSN and I need that. So this will combine the 2 fields to one
IF they have a real SSN. I used the ID field to link. I've created an SQL
that will do it if I name the tables. I want to insert the variable wordage
that I have added below the SQL.

SELECT GP1992Revised.ID, GP1992Copy.SSN, GP1992Revised.SSN,
IIf(([GP1992Revised]![SSN])="000000000",[GP1992Copy]![SSN],[GP1992Revised]![SSN])
AS Social, GP1992Revised.[First Name], GP1992Revised.[Last Name],
GP1992Revised.Gender, GP1992Revised.[Date of Birth], GP1992Revised.[Date of
Hire], GP1992Revised.Hours, GP1992Revised.Compensation,
GP1992Revised.[Deferral Amount], GP1992Revised.[Excludable Compensation],
GP1992Revised.[Section 125], GP1992Revised.[Status Code],
GP1992Revised.[Status Date]
FROM GP1992Copy RIGHT JOIN GP1992Revised ON GP1992Copy.ID = GP1992Revised.ID
WITH OWNERACCESS OPTION;

These are the two tables that should be used if I can get it to work.

[Forms]![fCensus1Conversion]![RunThisOne] & "Copy" (ex. GP1992Copy)

[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" (ex. GP1992Revised)

I replaced the items with this:

SELECT [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID,
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".SSN,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".SSN,
IIf(([Forms]![fCensus1Conversion]![RunThisOne] &
"Revised"![SSN])="000000000",[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"![SSN],[Forms]![fCensus1Conversion]![RunThisOne] & "Revised"![SSN]) AS
Social, [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[First Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Last Name],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Gender,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Birth],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Date of Hire],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Hours,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".Compensation,
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Deferral Amount],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Excludable
Compensation], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Section
125], [Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Code],
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".[Status Date]
FROM [Forms]![fCensus1Conversion]![RunThisOne] & "Copy" RIGHT JOIN
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised" ON
[Forms]![fCensus1Conversion]![RunThisOne] & "Copy".ID =
[Forms]![fCensus1Conversion]![RunThisOne] & "Revised".ID
WITH OWNERACCESS OPTION;

But when I run it I get an error: Syntax Error in From Clause

I'm sure it's something simple. I would really appreciate any help or
advice. Thanks in advance for your time.
Click to expand...

Bonnie,

I don't think the query can resolve a reference to a control on a form to
create a table name. You could use VB to create a string of the correct SQL,
then assign the string as the row source.

If the query is a saved query, you could modify the SQL of the query, then
open the form/report.

As a test, create a button on form "fCensus1Conversion".
Paste in the following code: (watch for line wrap!)

'--------------------------------
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.SSN, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.SSN, "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN])= '000000000', "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy![SSN], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised![SSN]) AS Social, "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[First Name], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Last
Name], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Gender, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Birth], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Hire], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Hours, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.Compensation, "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Deferral Amount], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Excludable Compensation], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Section 125], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Code], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date] "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy RIGHT
JOIN "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised ON "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.ID = "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'change "YourQuery" to the name of your query
Set qryTest = dbsCurrent.QueryDefs("YourQuery")
qryTest.SQL = strSQL

'other code

Set dbsCurrent = Nothing
Set qryTest = Nothing
'--------------------------------

Uncomment the MsgBox line or add a Debug.Pring strSQL line.

It should give you the correct SQL for the value selected in the
"RunThisOne" combo box.


HTH
Click to expand...
 
S

SteveS

Bonnie said:
SteveS, you are not only a genius, but a sharing genius. I could NEVER have
written the code you gave to me. AND, it works!!! But, of course, there are
the inevitable trailer questions.

I have 3 date fields in my export and need to format them to be dates only
rather than date/time. (Need to export 01/01/1945 rather than 01/01/1945
0:00:00.) How would I alter the lines? Here is one (I can do the rest). Or
is it done at the top of the function?

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date] "

I am SO amazed at how it went into an existing query and changed even the
base tables it was pulling from. I absolutely LOVE Access and am trying to
learn as much as I can. The VB part is mind-blowing.

I added a line at the bottom to run a transfertext macro but I'm getting an
error on naming the file. For some reason Access is saying "Run Time Error
3011" "The Microsoft Jet database engine can't find the object 'GP0013.csv'.
Make sure the object exists and that you spell it's name and file path name
correctly." Well, GP0013.csv is what the exported data file should be named
but it seems to think it should already be there like it's looking to get
something FROM it.

I have this in my Action Arguments of the TransferText macro
mData.Census1Done:

Transfer Type: Export Delimited
Specification Name: CensusExportSpec1
Table Name: qExportRevisedCensus1
File Name: ="S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv"
Has Field Names: No

The Table Name is the name of the 'thing' I am importing to or exporting
from so it should be the query that I want to run in Access to export my data
out to a comma delimited text file.

The File Name is the full path of the text file that I am importing from or
exporting to. (I have even tried keying just "GP0013.txt" into this line and
STILL get the same error.)

It seems that Access has it backwards and is saying it can't find the file I
want to IMPORT. Can you see if I've done something juvenile that I can't see
for the trees?

I'm SO close!!! Just need the date formating and be able to export the
file. If you have VB that will do it better than the macro, please feel free
to share it with me. I promise to use it and send positive thought waves out
into the world about you and your generosity.

Thanks again for your help and thanks in advance for any additional
assistance!
Click to expand...

Awww, gee Bonnie (blush blush). Thanks, but I don't know a tenth of one percent
of what the MPVs (insert names here) know. IMHO, this is the best NG on Access.

OK, on to your questions.....

You need to use the Format() function in the query to remove the time element.
For example, if you looked at the SQL of a saved query for a date field named
"DateWorked", it would look like

....., Format([DateWorked],"mm\/dd\/yyyy") AS DW, .....

(homework assignment: look up FORMAT() in help) <grin>

So, since I don't have your tables/queries, I'm guessing you would have to
modify the code to look like (should be one line):

strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date],"mm\/dd\/yyyy) AS StatusDate "

StatusDate won't have a comma at the end, but .[Date of Birth] and .[Date of
Hire] must have the commas.

---

In the NG there have been many discussions about to macro or not to macro; I
don't use macros because I think they are too limiting. Whatever you can do
with macros, you can do better with code.


There is a command to convert a macro to VB code (Tools/Macro). If I understand
what your macro is doing, this should work:

1) add Dim strFileName As String at the top of the SUB

2) modify the code to look like this:

..
..
..
'change "YourQuery" to the name of your query
Set qryTest = dbsCurrent.QueryDefs("YourQuery")
qryTest.SQL = strSQL

' new code

'create the path
strFileName = "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv"

'arguments of TransferText
'DoCmd.TransferText [transfertype][, specificationname], tablename,
filename[, hasfieldnames][, HTMLtablename][, codepage]

DoCmd.TransferText acExportDelim, "CensusExportSpec1",
"qExportRevisedCensus1", strFileName, False, ""

..
'rest of sub
..
..
..


----

I've never used "TransferText" before; any time I've had to export data, I
opened a recordset and looped thru it, writing a line at a time. Maybe not the
easiest (and way more code), but I had complete control of the data.

Anyway, let me know if this works.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Format problem 0
Permissions on SQL 2
Soooo Close...how to name file 2
Need to chg queried table 12
Need to name range/transfer spreadsheet 4
Code chgs table in query 1
Using a field on form as a parameter 8
Need to identify all edits 2

Top