Invalid Argument?!?

[Devo]

New at this...I'm trying to Export a query to a .txt file and I created this code based on a code I found online...I've tried a million different things and I always get the same error "invalid argument," I've thought through a few potential problems but to no avail...for instance...originally i didn't have the Cdate before the date column and I thought this was a sure fix since my date column is a varchar datatype. I then tried taking the #'s out from around the control references and it worked!...at least the .txt file was created but their was no data in the file!!! The #'s establish that the control reference is a date datatype...right? so I don't know why this would help. This is driving me crazy, can someone please help??? Thanks SO MUCH in advance! Here is the code:

Private Sub Command17_Click()
Dim PageName As String
On Error GoTo Err_Command17_Click

Dim rst As DAO.Recordset
Dim varArray As Variant
PageName = "I:\Shared_Retention\CCBatch\" & "CCBatch" & Format(Date, "MMDD") & ".txt"

Open PageName For Output As #1
Set rst = CurrentDb.OpenRecordset("Select * From dbo_creditcardrecords Where Cdate(Date)>=" & "#" & Forms!Reports!BeginningDate & "and Cdate(Date) <" & "#" & Forms!Reports!EndingDate & "#", dbSeeChanges)
While Not rst.EOF And Not rst.BOF
Print #1, rst
rst.MoveNext
Wend
Close #1
Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[gemma-the-husky]

just use docmd.transfertext to save your query


the other thing is in your code, you havent surrounded the frist date with the # symbol correctly, which is probably causing a problem

 

[rolaaus]

I had to double-check your dbSeeChanges argument. It looks like you have it in the wrong place. Just using the commas alone, nowadays when there are other methods available, can make life a bit hectic. I would mkae dbSeeChanges say Options:=dbSeeChanges. Since you aren't using named parameters, you would need to add (I believe) one more empty comma, but if you name the parameters, then you don't have to worry about skipping parameters - unless of course, they're mandatory.

 

[Devo]

Thanks guys, for the quick responses...
I actually had the # symbol arround the date correctly in the code, but I messed up as I was re-writing it here...Again, I am kinda a beginner here so let me verify what you are both saying...
I'm not exactly sure where "docmd.transfertext" should be placed...does this satisfy both of your remedies?
(am not at work so I cannot test it at the moment)

Private Sub Command17_Click()
Dim PageName As String
On Error GoTo Err_Command17_Click

Dim rst As DAO.Recordset
Dim varArray As Variant
PageName = "I:\Shared_Retention\CCBatch\" & "CCBatch" & Format(Date, "MMDD") & ".txt"

Open PageName For Output As #1
Set rst = CurrentDb.OpenRecordset("Select * From dbo_creditcardrecords Where Cdate(Date)>=" & "#" & Forms!Reports!BeginningDate & "# and Cdate(Date) <" & "#" & Forms!Reports!EndingDate & "#", , Options:=dbSeeChanges)

docmd.transfertext

While Not rst.EOF And Not rst.BOF
Print #1, rst
rst.MoveNext
Wend
Close #1
Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[rolaaus]

You are mis-using the docmd.transfertext
For help with this command, highlight it in your VBA code and press F1 - should jump to the right help topic.

using the TransferText as husky suggests is good if you are going to take your existing data in place it into a brand new file. However, if you are trying to append an existing spreadsheet file with new records (say, your database keeps track of your banking account activites, and you want to "back-up" your newest transactions for the day into a spreadsheet that already has all of your old transactions), then you would want to use the methodology you are currently employing. Of course, if your database tracked all transactions (referring to my example) then you wouldn't need to only export today's transaction, you could do a full back-up (rather than incremental), but I was using this example for example purposes only, not as a best-practice scenario.

 

[Devo]

docmd.transfertext

OK so I researched docmd.transfertext, and I now see what you're saying. I think I can work with this with my limited knowledge...but can you give me a quick example of how I would specify this query in the TableName argument. Naming the query didn't previously work for me, I believe because of the # symbol requirement. Thanks!

 

[rolaaus]

What you would most likely want to do is re-design your query so that the WHERE criteria is referencing the forms controls you want (i.e. Forms!Reports!EndingDate ) You can do this in a query itself, so long as the form is actually open and contains data - if you are using a click_event for a command button, or some other method to "launch" the export function, then you can have some code that validates those controls before running the export (transfertext) command.

In the query designer, you can right click the Criteria area and choose Build (make sure you are in the field you are wanting to filter, or you can cut/paste it later on if you accidently went into the wrong field). using the Builder you can navigate to the desired form and drop the control in as a criteria expression.

If you are basing this query on something that another object uses (like a report), then simply save it with a new name and only use it for this export feature - it may cause you to ahve to update multiple queries if you ever change the design of your database, but that's the risk that is run - and with proper planning, most design changes may never affect something that has already been built (ideally - hopefully ;-)

 

[Devo]

Ok...this docmd.transfertext is not workin for me...now I'm getting an error stating "couldn't find installable ISAM" I've researched this error and tried everything...un-installing and re-installing drivers in the data access options...and even un-installing and re-installing Access...I still cannot locate the dll's required. Anyway, I'm giving up on this method unless their is another possible fix. I tried your suggestion: ("I would make dbSeeChanges say Options:=dbSeeChanges.") and still get an error

"you must use the dbseechanges option with the openrecordset when accessing a SQL Server table that has an Identity column."

Here is the code with the adjustment:
Private Sub Command17_Click()
Dim PageName As String
On Error GoTo Err_Command17_Click

Dim rst As DAO.Recordset
Dim varArray As Variant
PageName = "I:\Shared_Retention\CCBatch\" & "CCBatch" & Format(Date, "MMDD") & ".txt"

Open PageName For Output As #1
Set rst = CurrentDb.OpenRecordset("Select * From dbo_creditcardrecords Where Cdate(Date)>= #" & Forms!Reports!BeginningDate & "# and Cdate(Date) < #" & Forms!Reports!EndingDate & "#", , options:=dbSeeChanges)

While Not rst.EOF And Not rst.BOF
Print #1, rst
rst.MoveNext
Wend
Close #1
Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub