Export Problem

[sk88]

I am in the line of finishing my access database but there is one issue where I could not solve and therefore I seek help from AccessForums.

I have tables like
table1:
homeID
name

table2:
problemID
problem

table3:
customerID


customerHome
customerProblem


and few other tables

now I have a form which ask questions like what kind of home and problem and the user can select from the drop down menu which is a lookup function of table1:home and table2:problem

everything is is TEXT. Even in table3, I made sure that the field type for customerHome and customerProblem is TEXT


But When I export them to excel, they show NUMBERS (that is the unique identifier) instead of TEXT.

I can't seemed to find the problem. I tried re-doing it but no luck... :S :(

any help?

[boblarson]

It is likely you have set it up incorrectly in that you have put in place lookups at TABLE level. They should not be in the tables. See here for more about that:
http://www.mvps.org/access/lookupfields.htm
But the export would need to be a QUERY that has all of the applicable tables in it and linked by ID but with the text value not from table 3 but from each of the respective other tables.

[ketbdnetbp]

A quick question: When you open table 3 is text being stored in the two fields you mentioned or are they numbers?

[boblarson]

A quick question: When you open table 3 is text being stored in the two fields you mentioned or are they numbers?

And make sure you know the difference between text being STORED or text being DISPLAYED. If you open the table and look and text is being displayed because you have lookups defined at the table level (as I pointed out) then it will LOOK like text is there when, in fact, numbers are being stored. So, ketbdnetbp, remember that looks can be deceiving. The way to know for sure would be to see if there is a drop down in that field when you open the table and click in that field. If there is, then a lookup is defined there when it should not.

[sk88]

This is like super strange.

I am attaching two image file here.

One from the query for export
and one from excel file when it is exported.

as you can see that only the first two of the HOME shows in TEXT.

But I did made sure that all my fields are in TEXT in all the tables

:S

[sk88]

.......
But the export would need to be a QUERY that has all of the applicable tables in it and linked by ID but with the text value not from table 3 but from each of the respective other tables.

I just tried linking the field from its respective tables in the query. It didn't work. Instead everything showed up.

[boblarson]

To be able to get this quickly resolved, the easiest way is going to be having you upload a copy of the database if you can.

[ketbdnetbp]

So, ketbdnetbp, remember that looks can be deceiving. The way to know for sure would be to see if there is a drop down in that field when you open the table and click in that field. If there is, then a lookup is defined there when it should not.

Bob, thanks for the clarification. Learning new things is what makes all this worthwhile and exciting!

All the best,

Jim

[sk88]

I realized what was missing in my VB codes.
I actually need to add a line to my VB codes for PROPER FORMATTING when exporting data to excel. I read somewhere that this way it would keep the lookup values the way it is because if it is done WITHOUT FORMATTING then the lookup ID are exported instead.

Sorry about earlier. I forgot to attach files when I post. I am new to this forum so I keep forgetting that I have to press UPLOAD


k.. I have created a module for exporting the data.

it is:
Public Sub ExportXLS2()

#If Not CC_Debug Then
On Error GoTo ErrProc
#End If

Const cQuery As String = "qryCapacityBuilding"


Dim fc As FileChooser
Dim strFileName As String

Set fc = New FileChooser
fc.DialogTitle = "Select file to save"
fc.OpenTitle = "Save"
fc.Filter = "Excel Files (*.xls)"
strFileName = Nz(fc.SaveFile, "")
Set fc = Nothing

' If user selected nothing or canceled, quit
If Len(strFileName) = 0 Then
Exit Sub
' If file already exists, delete it
ElseIf Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If

DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
cQuery, _
strFileName, _
HasFieldNames:=True
Format = !"(Home)"@




ExitProc:
Exit Sub
ErrProc:
ErrMsg Err, Err.Description, Err.Source
Resume ExitProc
End Sub




I also have another code which is linked to the Toggle Button

Private Sub Toggle17_Click()
#If Not CC_Debug Then
On Error GoTo ErrProc
#End If

ExportXLS2

ExitProc:
Exit Sub
ErrProc:
ErrMsg Err, Err.Description, Err.Source
Resume ExitProc
End Sub



With this, does anyone know where I should write a code for WITH FORMATTING and what ?

[sk88]

Sorry....SKIP/OMIT LINE which says "Format =!"( Home)"@ .." I was just trying out to see if I could format it but it did not work