?
Solved

Not displaying some VBA SQL criteria

Posted on 2011-09-22
3
Medium Priority
?
170 Views
Last Modified: 2012-05-12
Private Sub LBS(ocity As String, oprov As String, userinput As String)

  Dim sSQL As String, xcrit As String
   
    sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
    sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![MIN]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO =  [getDestinBeyond].[TCOMBI] & getDestinBeyond.[TCOMBI] "
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & " And P2CSameTx65Table.[Class] = " & [Forms]![Prov2Country]![classcombo] & ""
    
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

    
    CurrentDb.Execute sSQL, dbFailOnError

End Sub

Open in new window


Hi,

I have the above function which apends to a table. Now I want it to sort using class and order using class but I do not want to display the 'Class' column (or hide it)

Could you please let me know how to do this via VBA?

Thanks!
0
Comment
Question by:Shanan212
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 36582441
    sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([DESTIN CITY],"
    sSQL = sSQL & " [DESTIN PROVINCE], [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
    sSQL = sSQL & " SELECT P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
    sSQL = sSQL & " [P2CSameTx65Table]![MIN]+[getDestinBeyond]![MIN] AS [MIN],"
    sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinBeyond]![LTL] AS LTL,"
    sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinBeyond]![500] AS [500],"
    sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBeyond]![1M] AS [1M],"
    sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBeyond]![2M] AS [2M],"
    sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBeyond]![5M] AS [5M],"
    sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinBeyond]![10M] AS [10M],"
    sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinBeyond]![20M] AS [20M]"
    sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
    sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO =  [getDestinBeyond].[TCOMBI] & getDestinBeyond.[TCOMBI] "
    sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & " And P2CSameTx65Table.[Class] = " & [Forms]![Prov2Country]![classcombo] & ""
    
    sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"

Open in new window

0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 36582453
just remove the reference to P2CSameTx65Table.Class from line #8.

If you ONLY want to sort by class then change line 20 to:

    sSQL = sSQL & " ORDER BY P2CSameTx65Table.Class;"

0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36582708
Thanks!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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