Solved

Not displaying some VBA SQL criteria

Posted on 2011-09-22
3
164 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 400 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 (Access MVP)
Dale Fye (Access MVP) earned 100 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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