How to get selective data to show from multiselect

[captgnvr]

D/All

Some time ago I got help from this forum and this below code works fine to get all the data for the selected crew members from multiselect list box.

strSQL = "SELECT * FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "Q_MULTIVIEW"

This above code gives all the details about the crew.

Can you pls advice me how to get say only two fields like their 'date_join' and 'passport' if ten crew members are selected from the multilist listbox and send it to excel sheet.

 

[namliam]

replace the * in the sql by the fields you need/want

 

[captgnvr]

D/Namliam

strSQL = "SELECT TBL_CREW.NAME, TBL_CREW.RANK FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "Q_MULTIVIEW"

Thanks for the suggestion. When I changed the * and put the fields I wanted as mentioned above, I get a message saying that:


The expression on click.....: only comments may appear after End.....or endproperty.

* The expression may not result in the name of a macro.... or [event procedure]
* There may have been an error evaluating the function, even or macro.

Pls suggest/help.

 

[captgnvr]

D/Namliam

Yes yes. ur suggestion worked. Kept on trying and actually I found out by trial and error. It works fine with * but when I put the fields it was giving the error as mentioned above. Then when I exit that form containing multi select list and select the names again, it works like a charm. Thank you.

Can you tell me how to send this info thus obtained using the multilist to an excel sheet?

brgds/captgnvr

 

[namliam]

DoCMD.TransferSpreadsheet

 

[captgnvr]

D/Namliam

Thanks for the quick guidance. I am new to this and your one line advice is giving me places to look for. I am reading up the help on this and hope I figure it out. Will help a lot if any links on this 'transferspreadsheet' is given. wl rvrt. brgds/captgnvr

 

[captgnvr]

D/Namliam
I went thro the help file and did the below.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "TBL_CREW", "//RRM/SAA/ALLOT"

I checked and nothing transfered to this excel sheet. Also I need this filtered data into the 'allot' worksheet in sheet name 'acinfo'. Pls guide. brgds/captgnvr

 

[namliam]

D/Namliam
I went thro the help file and did the below.

Thats the place to go No link needed just the help

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "TBL_CREW", "//RRM/SAA/ALLOT"

I checked and nothing transfered to this excel sheet. Also I need this filtered data into the 'allot' worksheet in sheet name 'acinfo'. Pls guide. brgds/captgnvr

Not sure about the worksheet, but your syntax should be good to go, assuming the path you give is a proper UNC path.
Try sending it to C:\Temp\Test.xls instead to prevent issues with the UNC or rights.

 

[captgnvr]

D/Namliam

As u hv suggested I tried with c:\temp\test.xls. Still dont see any file in temp folder by test.xls file. Neither do I get any error when I click the command button. I just get the data filtered but the said data is not sent go excel.

 

[boblarson]

D/Namliam
I went thro the help file and did the below.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "TBL_CREW", "//RRM/SAA/ALLOT"

I checked and nothing transfered to this excel sheet. Also I need this filtered data into the 'allot' worksheet in sheet name 'acinfo'. Pls guide. brgds/captgnvr

Your acSpreadsheetTypeExcel97 should actually be acSpreadsheetTypeExcel8

 

[boblarson]

Your acSpreadsheetTypeExcel97 should actually be acSpreadsheetTypeExcel8

Well, I guess it shouldn't matter. Both return a value of 8.

Sorry about that.

 

[boblarson]

So this doesn't work?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel, "TBL_CREW", "C:\Temp\Text.xls"

 

[captgnvr]

D/Bob
Thank you. Just now managed to transfer the data to excel and what finally worked is the syntax:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "TBL_CREW", "\\RRM\SAA\UAT.XLS", True

But undesired results. It has sent the full crew data instead of the filtered one using multiselect list box crew names. Will you pls guide?? FYI, while going thro the other thread I even read your code for appending to existing sheet which I wl take it up after crossing this hurdle.

 

[boblarson]

But undesired results. It has sent the full crew data instead of the filtered one using multiselect list box crew names. Will you pls guide?? FYI, while going thro the other thread I even read your code for appending to existing sheet which I wl take it up after crossing this hurdle.

You are using the table name instead of the query. So replace TBL_CREW with the name of the query that is pulling the info you need.

 

[captgnvr]

D/Bob
I thanked and posted but that has gone missing and hence I am posting again. Thanks to you all, it is such an encouragement and pleasant to learn. Now I need to sort this data and hence where to put the code ORDER BY TBL_CREW.PRIORITY;.

How to put it at the end of
strSQL = "SELECT TBL_CREW.NAME, TBL_CREW.RANK,TBL_CREW.s_code FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ");"

 

[boblarson]

It would be:

strSQL = "SELECT TBL_CREW.NAME, TBL_CREW.RANK,TBL_CREW.s_code FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ") ORDER BY TBL_CREW.PRIORITY"

and you don't need the semi-colon at the end

 

[captgnvr]

D/Bob

After u suggesting the syntax and removing the semi colon, not getting errors but how to mention ascending order in the ORDER BY TBL_CREW.PRIORITY or decending if so required?

 

[boblarson]

D/Bob

After u suggesting the syntax and removing the semi colon, not getting errors but how to mention ascending order in the ORDER BY TBL_CREW.PRIORITY or decending if so required?

Ascending order is by default (you need not include that one) but to order by descending you would use:

strSQL = "SELECT TBL_CREW.NAME, TBL_CREW.RANK,TBL_CREW.s_code FROM TBL_CREW " & _
"WHERE TBL_CREW.NAME IN(" & strCriteria & ") ORDER BY TBL_CREW.PRIORITY DESC"

 

[captgnvr]

D/Bob
That did it. Now the finished data looks neat and rank wise sorted and as required. Appreciate your patience. Nice feeling of getting over one hurdle. Thank you.

 

[boblarson]

Don't forget to thank Namliam too.