Solved

Need assistance with modifying SQL statement (in form)

Posted on 2014-11-20
6
142 Views
Last Modified: 2014-11-20
Experts:

I need some assistance with tweaking a query (generated which is generated via a form).

Please see attached database (zipped).  In contains the following objects:

1. Table1
2. Query1
3. frmLogin

Process upon opening:
1. Open form "frmLogin"
2. Select any of the three values from the listbox
3. This will open Query1 and display the associated responses.

Here's what I need some help with:
- Tweak the SQL (in frmLogin) so that the selected value from the listbox (e.g., "Display 2-digit numbers)
is added as records (in new column [Selection]).
- Attached JPG "Query -- Current and New" illustrates the current and new query views.

Any idea as to how the SQL can be modified to achieve the new query display?

Thanks,
EEH
Query.zip
Query----Current-and-New.JPG
0
Comment
Question by:ExpExchHelp
[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
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40455233
Maybe this:

sql = "SELECT [" & strGenericField & "] as X_Digit, '" & Me.ListBoxTest.Value & "' as Selection FROM Table1"

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455239
Public Sub ListBoxTest_AfterUpdate()

On Error Resume Next
       Dim qd As DAO.QueryDef, db As DAO.Database, sql As String
       Dim strGenericField As String
       Set db = CurrentDb
       Set qd = db.QueryDefs("Query1")
       
       Select Case Me.ListBoxTest
            Case "Display 2-digit numbers": strGenericField = "2-DigitNumber"
            Case "Display 3-digit numbers": strGenericField = "3-DigitNumber"
            Case "Display 4-digit numbers": strGenericField = "4-DigitNumber"
       End Select
       
'       TempVars.Add "GenericField", Me.ListBoxTest.Value  <<< REVISED see next line
       TempVars.Add "GenericField", strGenericField
       sql = "Select ([" & strGenericField & "]) AS X_Digit FROM Table1"
       qd.sql = sql
       
       DoCmd.OpenQuery "Query1"

End Sub
0
 

Author Closing Comment

by:ExpExchHelp
ID: 40455240
James -- thank you... your solution is perfect!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:ExpExchHelp
ID: 40455245
Rey -- I just noticed your response.   I already had tested James' solution before I saw your feedback.

Thank you for chiming in... points already had been awarded though.  

Again, I thank you both for your assistance.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455249
correction, copied the wrong codes

Public Sub ListBoxTest_AfterUpdate()

On Error Resume Next
       Dim qd As DAO.QueryDef, db As DAO.Database, sql As String
       Dim strGenericField As String
       Set db = CurrentDb
       Set qd = db.QueryDefs("Query1")
       
       Select Case Me.ListBoxTest
            Case "Display 2-digit numbers": strGenericField = "2-DigitNumber"
            Case "Display 3-digit numbers": strGenericField = "3-DigitNumber"
            Case "Display 4-digit numbers": strGenericField = "4-DigitNumber"
       End Select
       
'       TempVars.Add "GenericField", Me.ListBoxTest.Value
       TempVars.Add "GenericField", strGenericField
'       sql = "Select ([" & strGenericField & "]) AS X_Digit FROM Table1"   <<< REVISED see next line
        sql = "SELECT [" & strGenericField & "] as X_Digit, '" & Me.ListBoxTest.Value & "' as Selection FROM Table1"
       qd.sql = sql
       
       DoCmd.OpenQuery "Query1"

End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40455251
np...
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compress Newid value ms sql Mssql 4 48
Combo box question 6 55
MS Access vba Print  PDF First page only 16 35
MS Access 2016 resize forms 3 12
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

752 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