Need date format in strSQL

[Bonnie]

Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00 rather than
just 10/01/1945. How do I format it? Here is one of my 3 lines with a date
in it:

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

Thanks in advance for any help or advice!

 

[Denis Dougall]

You could format that excel column to be a "short date" format or build it
into sql statement akin to
"Select * from MyTable where Format(Status Date, 'ddmmyyyy') "

HTH,

Denis

 

[Roger Carlson]

Without a context, this is difficult, but if I were to take a whack at it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Bonnie]

Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter an
existing query so it will find the 2 tables that are named with the value in
[RunThisOne]. It exports a comma delimited file. My 3 date fields are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie

Without a context, this is difficult, but if I were to take a whack at it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00 rather than
just 10/01/1945. How do I format it? Here is one of my 3 lines with a date
in it:

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

Thanks in advance for any help or advice!

 

[Roger Carlson]

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on the screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter an
existing query so it will find the 2 tables that are named with the value in
[RunThisOne]. It exports a comma delimited file. My 3 date fields are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie

Without a context, this is difficult, but if I were to take a whack at it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00

rather
than

just 10/01/1945. How do I format it? Here is one of my 3 lines with

a
date

in it:

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

Thanks in advance for any help or advice!

 

[Bonnie]

Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up. Can
you help? I just hope I've explained it well enough. Thanks again for your
help.
--
Bonnie

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on the screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter an
existing query so it will find the 2 tables that are named with the value in
[RunThisOne]. It exports a comma delimited file. My 3 date fields are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie

Without a context, this is difficult, but if I were to take a whack at it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00 rather
than
just 10/01/1945. How do I format it? Here is one of my 3 lines with a
date
in it:

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

Thanks in advance for any help or advice!

 

[Roger Carlson]

Oh, I see. The context I was referring to (and what I could not understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the SQL is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up. Can
you help? I just hope I've explained it well enough. Thanks again for your
help.
--
Bonnie

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on the screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter an
existing query so it will find the 2 tables that are named with the

value
in

[RunThisOne]. It exports a comma delimited file. My 3 date fields are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

Without a context, this is difficult, but if I were to take a whack

at
it,

I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00 rather
than
just 10/01/1945. How do I format it? Here is one of my 3 lines

with
a

date
in it:

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

Thanks in advance for any help or advice!

 

[Bonnie]

Hi again Roger, Yes, it's a table. I'm not getting the same error so I'm
pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I get the
error: Microsoft Visual Basic Run-time error '3011': The Microsoft Jet
database engine could not find the object 'GP0013.csv'. Make sure the object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank you SO
much for keeping the thread open. Thanks in advance for any additional help.
--
Bonnie

Oh, I see. The context I was referring to (and what I could not understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the SQL is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up. Can
you help? I just hope I've explained it well enough. Thanks again for your
help.
--
Bonnie

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on the screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter an
existing query so it will find the 2 tables that are named with the value
in
[RunThisOne]. It exports a comma delimited file. My 3 date fields are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

Without a context, this is difficult, but if I were to take a whack at
it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00
rather
than
just 10/01/1945. How do I format it? Here is one of my 3 lines with
a
date
in it:

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

Thanks in advance for any help or advice!

 

[Roger Carlson]

You are correct. If the file does not exist, it will create it. If the
file already exists, it will over-write it.

However, this confusing error can occur because of a variety of problems.
First thing to check is to run the query you're trying to export. If there
is a run-time error in the query, it can sometimes report this error
message.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi again Roger, Yes, it's a table. I'm not getting the same error so I'm
pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I get the
error: Microsoft Visual Basic Run-time error '3011': The Microsoft Jet
database engine could not find the object 'GP0013.csv'. Make sure the object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank you SO
much for keeping the thread open. Thanks in advance for any additional help.
--
Bonnie

Oh, I see. The context I was referring to (and what I could not understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the SQL is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up. Can
you help? I just hope I've explained it well enough. Thanks again

for
your

help.
--
Bonnie


:

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on

the
screen

which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to

alter
an

existing query so it will find the 2 tables that are named with

the
value

in
[RunThisOne]. It exports a comma delimited file. My 3 date

fields
are

exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

Without a context, this is difficult, but if I were to take a

whack
at

it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00
rather
than
just 10/01/1945. How do I format it? Here is one of my 3

lines
with

a
date
in it:

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

Thanks in advance for any help or advice!

 

[Bonnie]

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,
--
Bonnie

You are correct. If the file does not exist, it will create it. If the
file already exists, it will over-write it.

However, this confusing error can occur because of a variety of problems.
First thing to check is to run the query you're trying to export. If there
is a run-time error in the query, it can sometimes report this error
message.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi again Roger, Yes, it's a table. I'm not getting the same error so I'm
pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I get the
error: Microsoft Visual Basic Run-time error '3011': The Microsoft Jet
database engine could not find the object 'GP0013.csv'. Make sure the object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank you SO
much for keeping the thread open. Thanks in advance for any additional help.
--
Bonnie

Oh, I see. The context I was referring to (and what I could not understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the SQL is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra
quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up. Can
you help? I just hope I've explained it well enough. Thanks again for
your
help.
--
Bonnie


:

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on the
screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to alter
an
existing query so it will find the 2 tables that are named with the
value
in
[RunThisOne]. It exports a comma delimited file. My 3 date fields
are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the
S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis
missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

Without a context, this is difficult, but if I were to take a whack
at
it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00
rather
than
just 10/01/1945. How do I format it? Here is one of my 3 lines
with
a
date
in it:

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

Thanks in advance for any help or advice!

 

[Roger Carlson]

You know, one way to make it a little more managable is to read the value of
your textbox into a string variable and then use the string variable
everywhere you reference the textbox.

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim TableName as String

Set dbsCurrent = CurrentDb

TableName = [Forms]![fCensus1Conversion]![RunThisOne]
strSQL = "SELECT "

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

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

strSQL = strSQL & " FROM "
strSQL = strSQL & TableName & "Copy RIGHT Join "
strSQL = strSQL & TableName & "Revised ON "
strSQL = strSQL & TableName & "Copy.ID = "
strSQL = strSQL & TableName & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

One way to debug it is to put

debug.print strSQL

after the string is created and put a break point somewhere after this. The
SQL string as it will be seen by the database will go into the immediate
window. Copy and paste it into a new query in SQL View and try running the
query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,
--
Bonnie

You are correct. If the file does not exist, it will create it. If the
file already exists, it will over-write it.

However, this confusing error can occur because of a variety of problems.
First thing to check is to run the query you're trying to export. If there
is a run-time error in the query, it can sometimes report this error
message.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi again Roger, Yes, it's a table. I'm not getting the same error

so
I'm

pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I get the
error: Microsoft Visual Basic Run-time error '3011': The Microsoft Jet
database engine could not find the object 'GP0013.csv'. Make sure the object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank you SO
much for keeping the thread open. Thanks in advance for any

additional
help.

--
Bonnie


:

Oh, I see. The context I was referring to (and what I could not understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the

SQL
is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to

create
the

SQL, it should look something like this:

strSQL = strSQL & "Format(" &

[Forms]![fCensus1Conversion]![RunThisOne]
&

"Revised.[Status Date], ""mm/dd/yyyy"") "


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Morning Roger! Sorry for not understanding the context inquiry. Probably
why I'm still getting an error. I think it has to do with the extra
quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it

up.
Can

you help? I just hope I've explained it well enough. Thanks

again
for

your
help.
--
Bonnie


:

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows

on
the

screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using this

to
alter

an
existing query so it will find the 2 tables that are named

with
the

value
in
[RunThisOne]. It exports a comma delimited file. My 3 date fields
are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the
S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis
missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

Without a context, this is difficult, but if I were to take

a
whack

at
it,
I'd try:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hi folks! Using A02 on XP. Not a programmer but learning lots.

Have a .csv export and the date is landing as 10/01/1945 0:00:00
rather
than
just 10/01/1945. How do I format it? Here is one of my 3 lines
with
a
date
in it:

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

Thanks in advance for any help or advice!

 

[Bonnie]

Hey Roger! Guess what? I found the error! Went into the query design view
and guess what happened to my field [Status Date]? It looks like this:

Expr1: Format(GP0013Revised.[Status Date],"mm/dd/yyyy")

How do I put in the AS? I know there's supposed to be an AS [FieldName] in
there somewhere.

So excited I found it...
--
Bonnie

One way to debug it is to put

debug.print strSQL

after the string is created and put a break point somewhere after this. The
SQL string as it will be seen by the database will go into the immediate
window. Copy and paste it into a new query in SQL View and try running the
query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,
--
Bonnie

You are correct. If the file does not exist, it will create it. If the
file already exists, it will over-write it.

However, this confusing error can occur because of a variety of problems.
First thing to check is to run the query you're trying to export. If there
is a run-time error in the query, it can sometimes report this error
message.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi again Roger, Yes, it's a table. I'm not getting the same error so
I'm
pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I get the
error: Microsoft Visual Basic Run-time error '3011': The Microsoft Jet
database engine could not find the object 'GP0013.csv'. Make sure the
object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank you SO
much for keeping the thread open. Thanks in advance for any additional
help.
--
Bonnie


:

Oh, I see. The context I was referring to (and what I could not
understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in the SQL
is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create
the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Morning Roger! Sorry for not understanding the context inquiry.
Probably
why I'm still getting an error. I think it has to do with the extra
quotes
needed for text fields.

My form shows a contract record and the text field [RunThisOne] is the
contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding it up.
Can
you help? I just hope I've explained it well enough. Thanks again
for
your
help.
--
Bonnie


:

Showing the SQL is good. However, what I meant by context is what
[Forms]![fCensus1Conversion]![RunThisOne] is and what it shows on
the
screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using this to
alter
an
existing query so it will find the 2 tables that are named with
the
value
in
[RunThisOne]. It exports a comma delimited file. My 3 date
fields
are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the
S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis
missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

 

[Roger Carlson]

You're quite right and I should have remembered. Try something like this:

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

This MIGHT give you an error because you're using [Status Date] twice. If
it does try: [StatusDate] (no space)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hey Roger! Guess what? I found the error! Went into the query design view
and guess what happened to my field [Status Date]? It looks like this:

Expr1: Format(GP0013Revised.[Status Date],"mm/dd/yyyy")

How do I put in the AS? I know there's supposed to be an AS [FieldName] in
there somewhere.

So excited I found it...
--
Bonnie

One way to debug it is to put

debug.print strSQL

after the string is created and put a break point somewhere after this. The
SQL string as it will be seen by the database will go into the immediate
window. Copy and paste it into a new query in SQL View and try running the
query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,
--
Bonnie


:

You are correct. If the file does not exist, it will create it. If the
file already exists, it will over-write it.

However, this confusing error can occur because of a variety of problems.
First thing to check is to run the query you're trying to export.

If
there

is a run-time error in the query, it can sometimes report this error
message.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi again Roger, Yes, it's a table. I'm not getting the same

error
so

I'm
pretty sure it works. But now I'm getting an error on the export.

I have written this at the bottom of my VB (see my previous SQL text):

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1", "S:\RPS\PTS\CensusConversion\ToRelius\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False

MsgBox "Data has been exported to the S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

For some reason, it thinks the new file should already exist. I

get
the

error: Microsoft Visual Basic Run-time error '3011': The

Microsoft
Jet

database engine could not find the object 'GP0013.csv'. Make sure the
object
exists and that you spell its name and the path name correctly.

I know the file must exist to IMPORT but it shouldn't have to exist to
EXPORT. Can you see what I've done wrong?

You have been an absolute God-send on this project for me. Thank

you
SO

much for keeping the thread open. Thanks in advance for any additional
help.
--
Bonnie


:

Oh, I see. The context I was referring to (and what I could not
understand)
is that GP0013Revised is a table. Correct? This code inserts different
tables within the query dynamcially. What you *want* to see in

the
SQL

is:

format(GP0013Revised![Status Date], "mm/dd/yyyy")

which will format the date as you want it. So in your code to create
the
SQL, it should look something like this:

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


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Morning Roger! Sorry for not understanding the context inquiry.
Probably
why I'm still getting an error. I think it has to do with the extra
quotes
needed for text fields.

My form shows a contract record and the text field

[RunThisOne] is
the

contract number (letters 'GP' and 4 digits, for example GP0013).

I'm getting this Microsoft Visual Basic error: Run-time error '3075':
Syntax error (missing operator) in query expression 'GP0013
Format(Revised.[Status Date], "mm/dd/yyyy")'.

I am SOOOO close! I'm sure it's those quotes that are holding

it
up.

Can
you help? I just hope I've explained it well enough. Thanks again
for
your
help.
--
Bonnie


:

Showing the SQL is good. However, what I meant by context

is
what

[Forms]![fCensus1Conversion]![RunThisOne] is and what it

shows
on

the
screen
which would be interjected into your SQL statement.

You are absolutely right about the paren. It should be:
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
" Format(Revised.[Status Date], ""mm/dd/yyyy"") "

Again, this is just a guess.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Hi Roger,

Thanks for the reply. I'm getting an error. I am using

this
to

alter
an
existing query so it will find the 2 tables that are named with
the
value
in
[RunThisOne]. It exports a comma delimited file. My 3 date
fields
are
exporting time zeros after the date rather than just the date.

Wasn't sure what you meant by context below but here's my code:

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

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("qExportRevisedCensus1")
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.

DoCmd.TransferText acExportDelim, "RevisedCensus1ExportSpec",
"qExportRevisedCensus1",

"S:\RPS\PTS\CensusConversion\ToRelius\" &

[Forms]![fCensus1Conversion]![RunThisOne] & ".csv", False
MsgBox "Data has been exported to the
S:\RPS\PTS\CensusConversion\ToRelius
folder and named " & Me.RunThisOne & ".csv."

End Sub

I messed up the quotes or something. Isnt' there a parenthesis
missing?
Getting the missing operator run-time error 3075.

Thanks again for your help!
--
Bonnie


:

 

[Bonnie]

Hi Roger, Thanks so much for hanging with me on this. I'll try your wordage
below. Another question I've had through this thread is that I need the 3
date fields to export without the time zeros on the end. I just want
mm/dd/yyyy not nn/dd/yyyy 0:00:00. This part is driving me bonkers. Can you
give me any closure on this? Everything I try gives me an error.
--
Bonnie

You know, one way to make it a little more managable is to read the value of
your textbox into a string variable and then use the string variable
everywhere you reference the textbox.

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim TableName as String

Set dbsCurrent = CurrentDb

TableName = [Forms]![fCensus1Conversion]![RunThisOne]
strSQL = "SELECT "

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

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

strSQL = strSQL & " FROM "
strSQL = strSQL & TableName & "Copy RIGHT Join "
strSQL = strSQL & TableName & "Revised ON "
strSQL = strSQL & TableName & "Copy.ID = "
strSQL = strSQL & TableName & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,

 

[Douglas J. Steele]

Use the Format function (not the Format property) on those three fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Roger, Thanks so much for hanging with me on this. I'll try your
wordage
below. Another question I've had through this thread is that I need the 3
date fields to export without the time zeros on the end. I just want
mm/dd/yyyy not nn/dd/yyyy 0:00:00. This part is driving me bonkers. Can
you
give me any closure on this? Everything I try gives me an error.
--
Bonnie

You know, one way to make it a little more managable is to read the value
of
your textbox into a string variable and then use the string variable
everywhere you reference the textbox.

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim TableName as String

Set dbsCurrent = CurrentDb

TableName = [Forms]![fCensus1Conversion]![RunThisOne]
strSQL = "SELECT "

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

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

strSQL = strSQL & " FROM "
strSQL = strSQL & TableName & "Copy RIGHT Join "
strSQL = strSQL & TableName & "Revised ON "
strSQL = strSQL & TableName & "Copy.ID = "
strSQL = strSQL & TableName & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug
this
mug-ugly squeak.

Peace,

 

[Roger Carlson]

That was the original purpose of this thread, wasn't it? Try this:

strSQL = strSQL & "Format(" & TableName & "Revised.[Status Date],
""mm/dd/yyyy"") "

(Watch for word wrap. Should be all on one line.)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi Roger, Thanks so much for hanging with me on this. I'll try your wordage
below. Another question I've had through this thread is that I need the 3
date fields to export without the time zeros on the end. I just want
mm/dd/yyyy not nn/dd/yyyy 0:00:00. This part is driving me bonkers. Can you
give me any closure on this? Everything I try gives me an error.
--
Bonnie

You know, one way to make it a little more managable is to read the value of
your textbox into a string variable and then use the string variable
everywhere you reference the textbox.

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim TableName as String

Set dbsCurrent = CurrentDb

TableName = [Forms]![fCensus1Conversion]![RunThisOne]
strSQL = "SELECT "

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

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

strSQL = strSQL & " FROM "
strSQL = strSQL & TableName & "Copy RIGHT Join "
strSQL = strSQL & TableName & "Revised ON "
strSQL = strSQL & TableName & "Copy.ID = "
strSQL = strSQL & TableName & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks again Roger for keeping with this thread. I'll try to debug this
mug-ugly squeak.

Peace,

 

[Bonnie]

Hi Douglas,

I looked it up and found what I want: vbShortDate

But, where does it go in this line:

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

I guess I should remove "Format etc. but don't know how to alter the line.
Added the AS myself since the query seemed to like to call the field Exp1012
etc.

My other two date lines are:

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Birth], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Revised.[Date
of Hire], "

Would REALLY appreciate your help on this.

Thanks in advance for your time.
--
Bonnie

Use the Format function (not the Format property) on those three fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Roger, Thanks so much for hanging with me on this. I'll try your
wordage
below. Another question I've had through this thread is that I need the 3
date fields to export without the time zeros on the end. I just want
mm/dd/yyyy not nn/dd/yyyy 0:00:00. This part is driving me bonkers. Can
you
give me any closure on this? Everything I try gives me an error.
--
Bonnie

You know, one way to make it a little more managable is to read the value
of
your textbox into a string variable and then use the string variable
everywhere you reference the textbox.

Private Sub ExpRelius_Click()
Dim strSQL As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim TableName as String

Set dbsCurrent = CurrentDb

TableName = [Forms]![fCensus1Conversion]![RunThisOne]
strSQL = "SELECT "

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

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

strSQL = strSQL & " FROM "
strSQL = strSQL & TableName & "Copy RIGHT Join "
strSQL = strSQL & TableName & "Revised ON "
strSQL = strSQL & TableName & "Copy.ID = "
strSQL = strSQL & TableName & "Revised.ID "
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Thanks again Roger for keeping with this thread. I'll try to debug
this
mug-ugly squeak.

Peace,

 

[Douglas J. Steele]

First, that should have been:

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

And, to be honest, I'd keep mm/dd/yyyy for consistency reasons (unless you
really want to change it!)

Using Short Date means that it's dependent on the user's short date setting
(through Regional Settings). If someone else were to use your database and
their short date format is something different than yours, they'd get a
different result than you. That's not necessarily bad, of course...

If you do want to change, use

strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date], ""Short Date"") AS [StatusDate] "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

I looked it up and found what I want: vbShortDate

But, where does it go in this line:

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

I guess I should remove "Format etc. but don't know how to alter the line.
Added the AS myself since the query seemed to like to call the field
Exp1012
etc.

My other two date lines are:

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Date
of Birth], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Date
of Hire], "

Would REALLY appreciate your help on this.

Thanks in advance for your time.
--
Bonnie

Use the Format function (not the Format property) on those three fields.

 

[Bonnie]

Hi Douglas,

Thanks again for the info. I'm going to try the link next week. It'll work
much smoother as you advised earlier. Have a great weekend!

--
Bonnie

First, that should have been:

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

And, to be honest, I'd keep mm/dd/yyyy for consistency reasons (unless you
really want to change it!)

Using Short Date means that it's dependent on the user's short date setting
(through Regional Settings). If someone else were to use your database and
their short date format is something different than yours, they'd get a
different result than you. That's not necessarily bad, of course...

If you do want to change, use

strSQL = strSQL & "Format(" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Status Date], ""Short Date"") AS [StatusDate] "


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

I looked it up and found what I want: vbShortDate

But, where does it go in this line:

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

I guess I should remove "Format etc. but don't know how to alter the line.
Added the AS myself since the query seemed to like to call the field
Exp1012
etc.

My other two date lines are:

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Date
of Birth], "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Revised.[Date
of Hire], "

Would REALLY appreciate your help on this.

Thanks in advance for your time.
--
Bonnie

Use the Format function (not the Format property) on those three fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Roger, Thanks so much for hanging with me on this. I'll try your
wordage
below. Another question I've had through this thread is that I need
the 3
date fields to export without the time zeros on the end. I just want
mm/dd/yyyy not nn/dd/yyyy 0:00:00. This part is driving me bonkers.
Can
you
give me any closure on this? Everything I try gives me an error.