Query Acts Diffently depending on Export 2

[Ktx7ca]

Hello Everyone

I have this Query that pulls record between to dates:

SELECT Book.BookNumber, Book.DateUpdated, Book.Status
FROM Book
WHERE (((Book.DateUpdated) Between [Forms]![INS-Export]![EXPLastDate] And [Forms]![INS-Export]![INS-TodayDate] ) AND ((Book.Status)="Sold"));


When I export the results as a Delimited file all is well

DoCmd.TransferText acExportDelim, , "ExportSold", EXPFolderLocation & "SoldTest.TxT", -1

But when I export as an XML file it asks me to enter the dates like it can't find the txtboxes with the dates

Application.ExportXML acExportQuery, "ExportSold", EXPFolderLocation & "SoldTest.xml"


I run it form the same form as the DElim Export, It;s driving me up the wall.

Any Ideas?

 

[lameid]

Try specifying the Parameters...

[red]
PARAMETERS [Forms]![INS-Export]![EXPLastDate] DateTime,[Forms]![INS-Export]![INS-TodayDate] DateTime;[/red]
SELECT Book.BookNumber, Book.DateUpdated, Book.Status
FROM Book
WHERE (((Book.DateUpdated) Between [Forms]![INS-Export]![EXPLastDate] And [Forms]![INS-Export]![INS-TodayDate] ) AND ((Book.Status)="Sold"));

 

[Ktx7ca]

HI lameid

I gave that a try but no luck still the same.

very frustrating

 

[lameid]

Unfortunately I haven't dealt with XML but that seemed likely. The next thought I have is to build your SQL statment in code with the literals embedded and then export that SQL statement.

 

[dhookom]

Your process is having an issue resolving the references to the controls on the form.

I generally use the code solution as suggested by lameid. I have also created a date criteria table [tblDateCrit] with two fields [SDate] and [EDate] and only 1 record. Then you can enter the dates and use:

SELECT Book.BookNumber, Book.DateUpdated, Book.Status
FROM Book, tblDateCrit
WHERE (((Book.DateUpdated) Between [SDate] And [EDate] ) AND ((Book.Status)="Sold"));

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

When I make one record tables (for what would otherwise be unbound main forms is where I have used them), I add a boolean field with a default value of Yes and a validation rule = yes. This prevents you from accidentally having multiple records which can cause a whole other set of problems.

 

[dhookom]

Lameid,
Good practice. I assume the added field is the primary key?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Yes, typically I'd make it the PK. At the least I would make it a unique index in case something happened to the validation rule then you could only have two records.

In this scenario though, I'm thinking that making one or more criteria fields the PK may make a performance difference but that is more hunch than anything. I'd suspect if you matched the PK in the criteria field with the the field that is best indexed in the other table you would see best results. Since this is a range, it probably does not hold any value. I guess it depends on how smart Jet is with ANSI-89 non-Equijoins...

Access 2003 didn't like ANSI-92 syntax as that probably would force a performance difference if it worked.

 

[Ktx7ca]

Hello lameid and dhookom

Thanks for all your advice. I have gone with the Sql in code and it seams to work just fine. Your Idea with the single record table may help me with another issue I'm having and I will be giving it a try shortly.

You Both have beena great Help