How to export unbound columns to excel

[mkaeser]

Hello, I am using MS Access and Excel 2007. I have a query that I need to export to a specific sheet in excel, and the vba is working great and doing what it should. Problem is, some of the data is being exported as numeric, not as text which is what I need. I have several tables related to one another and the fields that were created with the LookUp Wizard and the ones that are not exporting property. From what I have read online, the data that is being exported is from the bound columns, which are the primary key "ID" fields. I have read online that I need to JOIN tables so that Access knows how to identify the foreign keys, but no matter how I do that, it is still exporting the "ID "fields and not the description. Perhaps this is not the right remedy for this problem? Does anyone have any ideas that might work? I would appreciate any and all feedback!

Here is the SQL that I am trying to export:

SELECT tblSampleNew.*, qryProjectSearch.Company, qryProjectSearch.ProjectName, qryProjectSearch.SampleDate, qryProjectSearch.SubmissionDate, qryProjectSearch.SamplePriority, qryProjectSearch.DueDate
FROM qryProjectSearch INNER JOIN tblSampleNew ON qryProjectSearch.IDNumber = tblSampleNew.IDNumber;

the qryProjectSearch.Company is one of the fields that is exporting incorrectly. I have tried to JOIN it to the original table by adding in this expression to the SQL above:

FROM qryProjectSearch INNER JOIN tblClients ON qryProjectSearch.Company = tblClients.ID

This gives a mismatch error, since one field is text type and the other is number, but even when I adjust that, it is still exporting as numbers and not text.

The annoying thing is that when I go to External Data --> Export to Excel Spreadsheet, every exports as text like I want! I feel like this should be a simple thing, any help is greatly appreciated!

 

[Isskint]

to get the value(text) related to a PK (or FK) you would need to add a DlookuP() - Dlookup("[Meaningfull text field]","[SomeTable]","[PK or FK]=" & [Pk or FK]). You would need to add this a calculated field in your query.

 

[mkaeser]

Thank you for the tip Isskint, I will try that out and hopefully that will solve this issue

 

[mkaeser]

Ok so I tried out this code a bunch of different ways. I realized that the first problem I was having was that I was adding the calculation to the query, but I wasn't deleting the original "Media" column so the query wasn't running correctly. I deleted the "Media" column and instead, added the expression with the title "Media". I tried the code a bunch of different ways and had success with the following:
Media: DLookUp ("[MediaType]","tblMedia", "ID=" & tblSampleNew.MediaType
This kinda works. Basically it is looking up the actual media name, instead of the ID number associated with the media type in the tblMedia. Unfortunately, it is running through every record in the tblMedia. So I am supposed to have 2 records output from the query, I am now getting 32......
I keep trying to switch this around but so far, I am not successful.....any advice on how I am doing this wrong? I feel like I'm right on the edge of getting it right!

 

[mkaeser]

I am thinking that I need to be more specific and not just use "ID=" & tblSampleNew.MediaType. I am thinking that since the values for this field are attached to the tblMedia values, that I should specify the column to reference? Something like "ID="tblSampleNew.MediaTypes.Column(1)? I am trying out variations of this now but I am not getting the right results, any suggestions?

 

[Isskint]

The use of ID's (Primary Keys & Foreign Keys) is at the foundation of a relational database.

So using DLookUp ("[MediaType]","tblMedia", "ID=" & tblSampleNew.MediaType is correct.

Lets just clarify what that statement should be doing. That statement is looking up the MediaType field in the tblMedia table where the ID field of tblMedia is equal to the value in the bound column (i say bound based on your subsequent comment of referring to .Column(1)) of the control MediaType on an object(form?) called tblSampleNew.
For this to work, ID and MediaType need to be the same format - probably Long Integer in this case? Also the statement will only work for the active record on tblSampleNew.

Would you be able to attach or provide a link to a copy of the database? Failing that screenshots of any relevant forms + table structure?

 

[mkaeser]

Yes that is the path I was hoping to take so I am glad I understood your previous advice correctly!

I think I figured out the issue, I kept the expression above and joined the 2 tables in my query as such

INNER JOIN tblMedia ON tblSampleNew.MediaType = tblMedia.ID

I assumed this relationship was already there since I originally used the lookup wizard to insert the tblMedia values into the tblSampleNew.MediaType field, but I guess not...

BTW both tblMedia and tblSampleNew are tables related to each other by field MediaType. The query is pulling values from tblSampleNew, which are newly entered samples, and joining them to another query that has project details. Afterwhich, I needed to export the output from the query to excel to a report template for the project. Now it seems like things are working, thanks a bunch for the help!

I tried uploading a screen shot to clarify things but it keeps rejecting my files