Solved

MS Access query displays form value, but not if you export to MS Excel

Posted on 2011-02-28
21
267 Views
Last Modified: 2012-05-11
I have a form with a field called start date and end date
I have a query where I am pulling in data based on the input values provided by the form.  When I run the query it was my intent to display the input values found within the form.  

Thus in the query I have 2 fields
StartDate:Forms!FormfrmMain[start date]
EndDate:Forms!FormfrmMain[end date]

When I run the query in design view the start date and end date appear.  If I export the query to Excel the start date and end date do not appear.  What am I doing wrong ?
0
Comment
Question by:pbo1
  • 9
  • 8
  • 3
  • +1
21 Comments
 
LVL 5

Expert Comment

by:tygrus2
ID: 35003494
You can put parts of the query SQL code in VBA code and have it delete&add the query each time.

strSQL = "SELECT Var1, Var2, Var3, #" & Forms!FormfrmMain[start date] "# as StartDate, #" & Forms!FormfrmMain[end date] "# as EndDate FROM table;"
db.QueryDefs.Delete "temp"
With db.CreateQueryDef("temp", strSql)
    .Execute
End With
0
 

Author Comment

by:pbo1
ID: 35003521
I don't understand the connection.  Are you indicating that I need to use VBA and delete the query in order for the values to display when the record-set is exported to Excel ?

Why do I see the values when I run the query in design, but not when I export the query to excel..thx :)
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35003689
I don't exactly understand your question. Where do the start date and end date appear when you run the query? Do you mean that it is used as parameters, but when you export to excel it just exports the whole query as if there were no parameters?
0
 

Author Comment

by:pbo1
ID: 35003800
No the parameters work.  The query retrieves all data based on the parameters.  However I wanted to add 2 columns that display the parameters.  Thus if I ask for all orders between Jan 1 and Jan 30 then if the returned record.set records will display Jan 1 in the first column and Jan 30 in the second column.  The remaining values are based on a table.  When I run the query in design view Jan 1 shows in the first column and Jan 30 in the second column and all the table driven values based on the paramaters.  Thus when I run in design view everything is perfect.  

If I try to export to excel then the 1 st and 2 nd column are blank, but other columns have values.  Thus I am perplexed why the 2 parameter values do not display when doing export to excel.  Does this help ?
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35004010
Just to check minor things first, it looks like you are missing "!" after the form names.

Change these:

StartDate:Forms!FormfrmMain[start date]
EndDate:Forms!FormfrmMain[end date]

to

StartDate:Forms!FormfrmMain![start date]
EndDate:Forms!FormfrmMain![end date]
0
 

Author Comment

by:pbo1
ID: 35004048
that was a typo on my part within the post..the query has correct syntax..in terms of !
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35004054
Are you also running MS Excel 2003?
0
 

Author Comment

by:pbo1
ID: 35007428
yes
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35011423
If you run the query in the datasheet view, do you get the right results?

If the query is returning the right results, do a manual copy and paste into Excel.
What you see in Access should be what you get in Excel

If a manual copy and paste gives the right results, then post what code you are using to export the query to Excel
0
 

Author Comment

by:pbo1
ID: 35011608
interesting...if I do a copy and paste the value does not display in excel..however the value is in datasheet view..
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 26

Expert Comment

by:Nick67
ID: 35011672
Then you've got something very weird on to go, because my Access2003/Excel2003 is WYSIWYG.

Now, in your db build a new, simple query just pulling dates and then the 'parameter' columns.
Does it copy and paste?

Try on another machine
Does it copy and paste?

Create a new mdb with just a little bit of data, and the same mechanics.
Does it copy and paste?

0
 

Author Comment

by:pbo1
ID: 35013991
Same thing happens on another computer.  I have attached the databsae ee.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35020400
Interesting.  It has to do with displaying dates.
Take your sample db and change the textboxes so they will accept text and fill in 'frig"
That displays in the query.
Formatted as date nothing shows
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35020477
Your sample wouldn't display dates for me in the query.
I am now outputting the working query to txt, and the parameters aren't going
ee.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35020488
they do paste into excel though
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35020653
This one exports properly to Excel as well as pasting properly.
The txt export doesn't carry the parameters though.

Try on your production one to remove the format property of "Short date" from the parameter textboxes and let me know  
ee.mdb
0
 

Author Comment

by:pbo1
ID: 35023614
I need to be able to use a date format...if I take the format off people will be inputting anything into the field.  There must be a reason and solution for this odd behavior ?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35028663
I don't know offhand, and I haven't been able to Google a reason.  My own production db's don't exhibit this behavior, but if I create a new db I encounter the same thing.

There are other ways to do data validation.  As part of the button click event, modifiy the following code, and duplicate it for both controls.  It will allow the data to be only a date, gives you control over the message the end user sees, and gets away from the nastiness that is input masks
'in the _Click event for the button
dim ctrl as control
set ctrl = me.start-date
if ValidateTheInput(ctrl) = false then exit sub
set ctrl = me.end-date
if ValidateTheInput(ctrl) = false then exit sub



'in the form's module 

Private Function ValidateTheInput(ctrl as control) as boolean
Dim SubmissionDate As String


BadDate:
SubmissionDate =  Format(ctrl.value, "dd-MMM-yy"))

'bail if it is ""
If SubmissionDate = "" Then Exit Sub

'validate the user input as a date
If IsDate(SubmissionDate) = False Then
    MsgBox "Your input was not seen as a vaild date.  Try again."
    ctrl.value = ""
    ctrl.setfocus
    GoTo BadDate 'throw them back if it is no good and not ""
End If

end sub

Open in new window

0
 

Author Comment

by:pbo1
ID: 35033336
I think I may try an intermediate step where I make a table and then export to excel..I will run and see if the date value gets passed to the table...
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35037966
I've got it, I think.
Check out
http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/27020/Date-Format-problem-in-parameter-query
Your sample db, when this is done to the query, quits returning wingdings.
I had tried this earlier, but I was use sd and ed in the parameters and not the [forms]![form1]![mycontrols] parameters
0
 

Author Closing Comment

by:pbo1
ID: 35041637
Excellent follow-up and persistence to an efficient solution.  I was just about to give up..
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now