unable to export to xml using a form field in where clause

[Obakeng1168]

I am trying to export a query to xml but when I hard code the filter on the vba it export, but when I want to use form it fails. the user will not have access to the code so its better if he can put parameter in the form and the code execute. This is my code, I use Access 2013

Application.ExportXML _
 ObjectType:=acExportQuery, _
 DataSource:="claim4", _
 DataTarget:="D:\OBELSOFT DEVELOPMENT\Xml tests\New folder\tblexportINV10.XML", _
WhereCondition:="claim4.transactionno > '" & Forms!claimxml2![txtInv] & "'"

please help

[twinnyfo]

Obakeng1168,

Welcome to Bytes!

Using Form values in strings can sometime produce unexpected results. This is why it is always better to declare a variable that uses that Form value and incorporate it into a string separately. Try this:

Dim strFilter As String
 
Strfilter = "claim4.transactionno > '" & Forms!claimxml2![txtInv] & "'"
 
Application.ExportXML _
    ObjectType:=acExportQuery, _
    DataSource:="claim4", _
    DataTarget:="D:\OBELSOFT DEVELOPMENT\Xml tests\New folder\tblexportINV10.XML", _
    WhereCondition:=strFilter

Also, it looks like you are using a numerical value for transactionno? If this is the case, line 3 above would be:

Strfilter = "claim4.transactionno > ' & Forms!claimxml2![txtInv]

(No need for single quotes with numerical values.)

Hope this hepps!

[Obakeng1168]

Thanks a lot, it worked for me perfectly. Now how can I make the exported xml file to leave out "dataroot" line and replace it with my own line like "BatchClaim" both from the top and bottom of the xml document.

[Obakeng1168]

Sorry one other thing how do I make my date to display only date not date and time 2018-12-28T00:00:00 in the xml file

[twinnyfo]

Obekeng1168:
Now how can I make the exported xml file to leave out "dataroot" line and replace it with my own line like "BatchClaim" both from the top and bottom of the xml document.

I have no idea what you are talking about.

Obekeng1168:
how do I make my date to display only date not date and time 2018-12-28T00:00:00

Change the format in your query using the Format() function.

[Obakeng1168]

<?xml version="1.0" encoding="UTF-8"?>

-<dataroot generated="2019-03-01T15:07:10" xmlns:od="urn:schemas-microsoft-com:officedata">


-<claimBatch>

<Transactionno>973</Transactionno>


-<Batchheader>

<BatchNo>7</BatchNo>

<BatchDate>2019-03-01T00:00:00</BatchDate>

<schemecode>Pula</schemecode>

<Source>58010</Source>

<Transactionno>973</Transactionno>

</Batchheader>

That the dataroot line in xml file

[twinnyfo]

Apparently that line is generated by the system itself. It is not part of your exported query, so apparently there isn't anything you can do about it on the front end. You might be able to work with it after the fact. Is there a reason you need to remove it?

Concerning the date, if you can isolate that string, you can remove everything after the "T". This gives you just the date. But again, that is after the fact. Obviously you are using this data after it is exported to xml?

[Obakeng1168]

Hello team

I am using 5 queries to generate xml file, I managed to use Format([invoicedate],"dd\/mm\/yyyy") to change the date from including time, but when I put it in other queries which also have date which is including time it refuses to export.

Can anyone assist me

[twinnyfo]

I will recommend you begin a new thread and in that new thread provide more detailed information than what you have provided here. To say that you are using 5 queries and putting one query with the others does not help us trouble shoot. Please post the queries in this new thread, allong with sample data that you are using and a detailed description of intended results and actual results.

Thanks.