exported query to excel with dates range file name (1 Viewer)

gippsy · Feb 25, 2018

Hello

On 03-25-2017 sneuberg gently helped me with above code.
With this code I export a query to excel. If for example I search by day1 20_02_2018 and day2 20_02_2018 the code assign the exported file name as 20_02_2018_qry_01.

I need the code to include in the exported file name the plot code such: 20_02_2018_qry_01_6

On the other hand, when a user enters to dates and plot code such as:
Day1 05/02/2018 and Day2 20/02/2018 and plot 2

In the exported query I need the file name as: 05_02_2018- 20_02_2018_2.

I really appreciated your help

Code:

Private Sub cmb_export_excel_bydatesrange_plot_Click()
Dim filename1 As String

If DCount("*", "qry_01") <> 0 Then
filename1 = "C:\Path\" & Replace(Me.txtday1, "/", "_") & "_qry_01.xlsx"
        DoCmd.OutputTo acOutputQuery, "qry_01", acFormatXLSX, filename1, False
        MsgBox "Query already exported to Excel"

    Else
        MsgBox "This range has no records"
        DoCmd.Close acQuery, "qry_01"
    End If
End Sub

Gasman · Feb 25, 2018

Test to see if the start and end dates are the same and build the filename string depending on that. Then append the text "_qry_01", then the plot number and finally the ".xlsx"

gippsy · Feb 25, 2018

Gasman. Many thanks for your help.
I tried this code but does not work.
filename1 = "C:\Path" & Replace(Me.txtday1, "/", "_") & Replace(Me.txtplot, "/", "_" & "_") & "_"_"_qry_01.xlsx".

Could you tell me what I am doing wrong?

Mark_ · Feb 26, 2018

I don't think he can tell you unless you clearly identify how you get a start and end date and tell us what

I tried this code but does not work.

means. Explain HOW it does not work, if there is an error in what is returned, ect.

gippsy · Feb 26, 2018

June7 and Mark. Many thanks for your reply and help.

I attach the Database I am working.

The code in Command Button "Export All to Excel" runs well
The code in Command Button "Export All to Excel by Dates Range" runs well. However, I need the exported file name including the date range (dd_mm_yyyy-dd_mm_yyyy).

The code in Command Button "Export All to Excel by Dates Range and Plot" runs well. Nevertheless, here is where I am trying the code that if the user enters same date, the exported file name must be dd-mm-yyy_qry_01_plot.

But if user different dates, the exported file name must be dd-mm-yyyy-dd-mm-yyyy_qry_01_plot.

In addition, my DataBase is in folder "qrys_day" located in Desktop. If I use my Database on another computer, do I have to be always writing the Path or there is a code to define the Path to export files to any computer. I have been searching the web with no success.:banghead:

Your help is really welcome.

BoBaxter · Feb 26, 2018

For the first part I would use something like

Code:

if me.txtday1 = me.txtplot then
filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & "_"_"_qry_01.xlsx".
else
filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & Replace(Me.txtplot, "/", "_" & "_") & "_"_"_qry_01.xlsx".
end if

For the second part of your question, instead of

Code:

filename1 = "C:\Path" & Replace(Me.txtday1, "/", "_") & Replace(Me.txtplot, "/", "_" & "_") & "_"_"_qry_01.xlsx".

You could use CurrentProject.Path, that will find where the open database is stored and should work on any computer.

Code:

filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & Replace(Me.txtplot, "/", "_" & "_") & "_"_"_qry_01.xlsx".

Gasman · Feb 26, 2018

What is all this?

Code:

& "_"_"_qry_01.xlsx"

what is wrong with "_qry_01.xlsx"

BoBaxter · Feb 26, 2018

I copied and pasted what you had earlier. To remove those just use

Code:

if me.txtday1 = me.txtplot then
filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & "_qry_01.xlsx".
else
filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & Replace(Me.txtplot, "/", "_" & "_") & "_qry_01.xlsx".
end if

BoBaxter · Feb 26, 2018

So this should be your final product on it. I should have been more clear before on this one.

Code:

Private Sub cmb_export_excel_bydatesrange_plot_Click()
Dim filename1 As String
If DCount("*", "qry_01") <> 0 Then
 if datevalue(me.txtday1) = datevalue(me.txtplot) then
  filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & "_qry_01.xlsx"
 else
  filename1 = CurrentProject.Path & "\" & Replace(Me.txtday1, "/", "_") & " - " & Replace(Me.txtplot, "/", "_" & "_") & "_qry_01.xlsx"
 end if
        DoCmd.OutputTo acOutputQuery, "qry_01", acFormatXLSX, filename1, False
        MsgBox "Query already exported to Excel"
    Else
        MsgBox "This range has no records"
        DoCmd.Close acQuery, "qry_01"
    End If
End Sub

Gasman · Feb 26, 2018

Sorry BoBaxter, that was addressed to the O/P

BoBaxter · Feb 26, 2018

My bad and no worries. This is all kinda new to me as far as posting on here, but I use this site all the time for reference. It kept saying I should post and try to help so I am giving it a college try, but I have to say trying to write code on here is certainly a new skill to me.

Pat Hartman · Feb 26, 2018

I hope you have your answer but I'd like to take this as a teaching moment.
Format() converts whatever you are formatting to a string so although -
format(me.txtday1,"mm/dd/yyyy") = format(me.txtplot,"mm/dd/yyyy")
will work, substituting > or < for the = sign will produce incorrect results because
02/12/2017 will be > 01/01/2018 Because strings are compared character by character, left to right. So, never turn a date into a string if you want to compare it to another date or sort it. If you do, you should format it as yyyymmdd because that string WILL sort and compare correctly because it orders the components of a date from most to least important.

It is always best to use a date function rather than a string function when working with dates so if you know your date field might contain time and you don't want time to influence the result, use the DateValue() function. This will extract only the date part of the field's value and of course TimeValue() will extract only the time part.

Also, I wouldn't use the Replace() function on anything other than a string. It may work today but since it is a string function, it could break some time in the future if MS decides to tighten up the rules in VBA. We've seen this happen multiple times over the years. This is a case where I would use Format() because I want the date field to be converted to a string. Again, I would always make the field order y,m,d because if i want to sort the list, I'd rather have all of 2018 together than all the Feburary 26ths for all years.

BoBaxter · Feb 27, 2018

thanks pat. that was a good explanation. I converted it to that format because I wasn't sure if the op was using a general date or a date time field. A date time field will fail sometimes if the hour or minutes are off comparatively. Your explanation was great though.

on a side note I used to work in Stratford ct at the movie theater on rte 1. I'm in Miami now but it was a good job while I was in college.

Pat Hartman · Feb 27, 2018

I saw many movies in that theater. It is now a gym.

gippsy · Feb 28, 2018

Pat.
As I am improving my knowledge in vba, your explanation is clear and very useful.
Thank you very much

gippsy · Feb 28, 2018

BoBaxter

I ran the code you recommend me. However, anytime I enter the same date or different dates, vba displays error 13 in this line:
If DateValue(Me.txtday1) = DateValue(Me.txtplot) Then

I have been trying by my self to fix such error with no success.

Could you suggest me how to fix the code?

Once again many thanks

PS. have been facing problems to attach the image to this message. Anytime I try to upload the image or to send the message the page web page falls.

gippsy · Feb 28, 2018

BoBaxter.

In my previous message, the access-programmers website didn´t allow to send the image that illustrates the error.

Gasman · Feb 28, 2018

gippsy,
Take a step back and look at what you are trying to do.

You need to compare your dates. I *thought* plot was just a number?, not a date.

So take it in small steps.
Compare your dates. If the same just build your filename with the one date, else build it with first date, then last date.
Then add what is common to the file name, the "qry01" and then the plot number.

Work out in your head how you would do it on paper, then try and then try and do the same in code.

Error 13 is a type mismatch error.

exported query to excel with dates range file name (1 Viewer)

gippsy

Registered User.

Gasman

Enthusiastic Amateur

gippsy

Registered User.

Mark_

Longboard on the internet

gippsy

Registered User.

BoBaxter

Registered User.

Gasman

Enthusiastic Amateur

BoBaxter

Registered User.

BoBaxter

Registered User.

Gasman

Enthusiastic Amateur

BoBaxter

Registered User.

Pat Hartman

Super Moderator

BoBaxter

Registered User.

Pat Hartman

Super Moderator

gippsy

Registered User.

gippsy

Registered User.

gippsy

Registered User.

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread