Solved How to Export Current Recordset and remove certain fields to Excel (Field/Column Filtering) (1 Viewer)

[theDBguy]

Unwanted Fields:
PO, DUEDATE, CREDITNOTE, INVTTLEXVAT,PAYID, AMTPD, EXPID, MTHEND, ENTRYDATE, FILE, DAYSDUE, SUPID, SUPPLIER

Thanks. Quick question: Did you want the headers exported as well?

 

[raziel3]

Thanks. Quick question: Did you want the headers exported as well?

Yes.

 

[theDBguy]

Yes.

Thank you. I was already in the middle of creating something before you provided the list of unwanted fields, so I may have gone about it the opposite direction as you were intending. However, it shouldn't be too hard to reverse the process. Please take a look at the attached and let us know how it goes.

 

[raziel3]

Thank you. I was already in the middle of creating something before you provided the list of unwanted fields, so I may have gone about it the opposite direction as you were intending. However, it shouldn't be too hard to reverse the process. Please take a look at the attached and let us know how it goes.

That's really good.
Just what I'm looking for. Will test and update the thread.

 

[theDBguy]

That's really good.
Just what I'm looking for. Will test and update the thread.

Good luck!

 

[raziel3]

@theDBguy Hello. Having one little problem.

"WHERE (MTHEND = #11/30/2023#) AND ([Search].[VENDISP] Like "*GENERAL*")"

If I do a filter on the Form itself, not the underlying query, the export is not working.

So this ([Search].[VENDISP] Like "GENERAL*") is happening
instead of this (qrySearch.[VENDISP] Like "GENERAL*")

Using the search boxes causes no issues.

 

[theDBguy]

@theDBguy Hello. Having one little problem.

"WHERE (MTHEND = #11/30/2023#) AND ([Search].[VENDISP] Like "*GENERAL*")"

If I do a filter on the Form itself, not the underlying query, the export is not working.

So this ([Search].[VENDISP] Like "GENERAL*") is happening
instead of this (qrySearch.[VENDISP] Like "GENERAL*")

Using the search boxes causes no issues.

I just gave it a try, and it worked fine for me. You'll have to give me the steps to duplicate the problem. This is what I did.

1. From the Home form, clicked on VIEW PO/INVOICE button
2. From the Search form, clicked on Reset button
3. Right-clicked on SUPPLIER/PAYEE and applied a filter to a single vendor (i.e. VENDISP='VENDOR-01')
4. Clicked on Export to Excel button, and everything went through

I tried to use LIKE *GENERAL* but no records showed up.

 

[raziel3]

I just gave it a try, and it worked fine for me. You'll have to give me the steps to duplicate the problem. This is what I did.

1. From the Home form, clicked on VIEW PO/INVOICE button
2. From the Search form, clicked on Reset button
3. Right-clicked on SUPPLIER/PAYEE and applied a filter to a single vendor (i.e. VENDISP='VENDOR-01')
4. Clicked on Export to Excel button, and everything went through

I tried to use LIKE *GENERAL* but no records showed

The db was a scaled down version. I have to send you one with more records.

 

[theDBguy]

The db was a scaled down version. I have to send you one with more records.

I'm not sure more records will make a difference. I just tried applying Contains "17" (VENDISP LIKE "*17*"), and it still worked, filtering all records to "VENDOR-17"). I'll need the exact steps you took to apply your filter. Did you also use right-click, like I did?

 

[raziel3]

I'm not sure more records will make a difference. I just tried applying Contains "17" (VENDISP LIKE "*17*"), and it still worked, filtering all records to "VENDOR-17"). I'll need the exact steps you took to apply your filter. Did you also use right-click, like I did?

yes. in the debugging window in the pic i attached, look at "strFilter". Right clicking on your version works fine.

 

[theDBguy]

yes. in the debugging window look at "strFilter"

I just did, and here's what I got.

Why can't you answer my question? How exactly did you apply your filter to the form?

I originally considered this situation when I was designing the code, but I thought since you're using a continuous form, I thought you probably won't run into this issue. Otherwise, if you were using a datasheet form, I would have probably add some more checks for that.

 

[raziel3]

I just did, and here's what I got.
View attachment 111202
Why can't you answer my question? How exactly did you apply your filter to the form?

I originally considered this situation when I was designing the code, but I thought since you're using a continuous form, I thought you probably won't run into this issue. Otherwise, if you were using a datasheet form, I would have probably add some more checks for that.

I applied one filter in the Cheque Number text box and right clicked Supplier/Payee filtered "Contains GENERAL".

 

[raziel3]

Filtered the form then clicked to export

 

[theDBguy]

Filtered the form then clicked to export

And here's what I got...

No errors! You must have made some changes to my code or something. Try doing that in the same file I sent you - the one that you haven't modified yet and let me know if you still get the same error.

 

[raziel3]

@theDBguy I figured it out. I had remove the existing query from the form and make qrySearch the form's RecordSource.

Sorry about that.

 

[theDBguy]

@theDBguy I figured it out. I had remove the existing query from the form and make qrySearch the form's RecordSource.

Sorry about that.

Glad to hear you got it sorted out. The only reason I used a saved query object was because you had an ORDER BY clause in your form's recordsource on a calculated column, which was not included in the exported recordset. That caused an error unless we remove the sort order, which would then result in an Excel file that is not sorted.