By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

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

P: 9
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

Expand|Select|Wrap|Line Numbers
  1. Application.ExportXML _
  2.  ObjectType:=acExportQuery, _
  3.  DataSource:="claim4", _
  4.  DataTarget:="D:\OBELSOFT DEVELOPMENT\Xml tests\New folder\tblexportINV10.XML", _
  5. WhereCondition:="claim4.transactionno > '" & Forms!claimxml2![txtInv] & "'"
please help
4 Weeks Ago #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. Strfilter = "claim4.transactionno > '" & Forms!claimxml2![txtInv] & "'"
  4.  
  5. Application.ExportXML _
  6.     ObjectType:=acExportQuery, _
  7.     DataSource:="claim4", _
  8.     DataTarget:="D:\OBELSOFT DEVELOPMENT\Xml tests\New folder\tblexportINV10.XML", _
  9.     WhereCondition:=strFilter
Also, it looks like you are using a numerical value for transactionno? If this is the case, line 3 above would be:

Expand|Select|Wrap|Line Numbers
  1. Strfilter = "claim4.transactionno > ' & Forms!claimxml2![txtInv]
(No need for single quotes with numerical values.)

Hope this hepps!

Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,054
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2.  
  3. Strfilter = "claim4.transactionno > '" & Forms!claimxml2![txtInv] & "'"
  4.  
  5. Application.ExportXML _
  6.     ObjectType:=acExportQuery, _
  7.     DataSource:="claim4", _
  8.     DataTarget:="D:\OBELSOFT DEVELOPMENT\Xml tests\New folder\tblexportINV10.XML", _
  9.     WhereCondition:=strFilter
Also, it looks like you are using a numerical value for transactionno? If this is the case, line 3 above would be:

Expand|Select|Wrap|Line Numbers
  1. Strfilter = "claim4.transactionno > ' & Forms!claimxml2![txtInv]
(No need for single quotes with numerical values.)

Hope this hepps!
4 Weeks Ago #2

P: 9
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.
3 Weeks Ago #3

P: 9
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
3 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
3 Weeks Ago #5

P: 9
<?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
3 Weeks Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,054
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?
3 Weeks Ago #7

P: 9
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
2 Weeks Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
2 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.