write sql statement to row source

I'm trying to use an on click event in one list box to change the contents of another list box. The code I'm using re-writes the sql code to the row source property if I enclose the table names in square brackets, but doesn't generate known results. Similar SQL without the square brackets in the row source will generate the known results, won't write from the vba code.

This may be similar to what Alistair69 was trying to do.

Any help will be appreciated
Rich

Here is the code:

	Code:
	Private Sub TitleList_Click()
On Error GoTo Err_TitleList_Click

Dim vSearchString, x As String
vSearchString = Me.TitleList.Column(1)

Dim abc As String
abc = "SELECT [COPY].CopyKey,"
abc = abc & "[COPY].CopyID,"
abc = abc & "[TITLE].Title,"
abc = abc & "[TITLE].Edition"
abc = abc & " FROM ([COPY] INNER JOIN ON [COPY].FK_ISBN=[TITLE].PK_ISBN WHERE ((([COPY].CopyKey) 
Not In (SELECT FK_CopyKey From ISSUES_to_STUDENT Where DateIn Is NULL)))"
abc = abc & "AND (([Title].Title Like " & vSearchString & "));"Me.FK_CopyKey.RowSource = abc
Me.FK_CopyKey.Requery
Me.Refresh

Exit_TitleList_Click:
Exit Sub

Err_TitleList_Click:
MsgBox Err.Description
Resume Exit_TitleList_Click

End Sub

Using Access 2003/2000 winxp


Post your answer or comment

comments powered by Disqus
I have an SQL statement that I want to display to a top 25 on a listbox on my form. I think the SQL may be ok but I'm not sure why it won't show up in the listbox. I have the listbox properties set to:
Row Source Type: Table/Query
Column Count: 5

My Code is:

Private Sub cmdGetTopTenFBERCells_Click()

Me.lstCoverListbox.Visible = False
Me.lstTopTenForwardBER.Visible = True
Me.lstTopTenForwardBER.SetFocus

Dim sdate, smode, switch As String
Dim i, j As Integer

sdate = Me.cboDate
smode = Me.cboMode
switch = Me.cboSwitch

Me.lstTopTenForwardBER.RowSource = " SELECT TOP 25 Alex.[Cell-ID] AS [CELL], Alex.[Digital Call-Volume] AS [Dig Call Volume], Alex.[FBER-DROP-Attempts] AS [FBER Drop Attempts], Alex.[Ratio FBER DROP Attemps to Call Volume] AS [FBER Drop Attemps/Call Volume], Alex.Date AS [Date]" & _
"FROM Alex" & "ORDER BY Alex.[Ratio FBER Drop Attemps to Call Volume] DESC;"
Me.Refresh

End Sub

I'm sure I'm probably missing something simple. Thank you for any help.

I am trying to re-write the following SQL statement to eliminate the "where" part of the statement, as we need to use the value in single-cell table. However, I am having trouble getting the exact results, and I think the problem is with my joins.

Original SQL:

	Code:
	select oi.iep_offer_nbr,p.old_item_nbr,p.item_status_cd
from iep_offer_invstmnt oi,iep_prod p
where oi.iep_offer_nbr  = 3200024
and oi.old_item_nbr = p.old_item_nbr
and p.ITEM_STATUS_CD  NOT IN  ( 'O','S','A'  )

1611 Row(s) affected

Modified SQL:

	Code:
	SELECT DISTINCT
  ADMIN.IEP_PARALLEL_PRCSNG.IEP_OFFER_NBR,
  ADMIN.IEP_PROD.OLD_ITEM_NBR,
  ADMIN.IEP_PROD.ITEM_STATUS_CD
FROM
  ADMIN.IEP_PARALLEL_PRCSNG INNER JOIN ADMIN.IEP_OFFER_INVSTMNT ON
(ADMIN.IEP_OFFER_INVSTMNT.IEP_OFFER_NBR=ADMIN.IEP_PARALLEL_PRCSNG.IEP_OFFER_NBR)
WHERE
  ADMIN.IEP_PROD.ITEM_STATUS_CD  NOT IN  ( 'O','S','A'  )



Have to say first of all folks that only been on here for a day or 2 and the info that can be found is invaluble to an inexperienced newbie to Access such as myself !!!

The particular problem I'm trying to over-come at the moment is that of an SQL Statement used to populate a list box within a form, I am trying to make it possible so that a that a user can open a second form from with fields auto-filled etc...

Now for the crux...

On the first form (frmDealers1) the user enters either a UniqueID (DID#) or Dealer Name (Client) into an unbound txt Box that applies a filter to my list box or the user can simply scroll thourgh the List Box, problem here is twofold really

I can type in a Dealers Name and the Filter works fine, but there is 3800 Dealer Names within the total list...and some of the names are not unique!

Secondly I am unable to use the DID# to locate the Dealers, i.e i cannot use the DID# to look up records?

Below is a copy of the SQL Statement I have used:

SELECT Dealers.Client, Dealers.dealerID FROM Dealers WHERE (((Dealers.dealerID) Like Forms!frmDealers!txtSearchBox & "*")) Or (((Dealers.Client) Like Forms!frmDealers!txtSearchBox & "*"));

The List Box only displays Dealer Names and not DID# aswell, however if I change the positions of the fields in the SQL Statement Builder (1st is Dealers 2nd is DID#...if these are swapped) then only the DID# is displayed and the txt Box will only except a search by DID#.............

Have tried replacing the "OR" with "AND" in the syntax but with no success!!

Maybe had better include the code for the opening of my second form aswell to elaborate more...

Private Sub lstDealers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDealers1", acNormal, , "client = '" & Me.lstDealers & "'"

This probably is a confusing way of trying to explain things on my side, but if anyone is able to understand this, then any help offered is MOST gratefully received !!!

I have never used SQL in codes before. I don’t know the syntax or the structure of writing a SQL statement in codes. Every time I need to check a value, I would write a query to pass the value of a field to a text box of a form, then in the code, I would reference that text box. It is not efficient and I always need to have that form open.


If this has been discussed, I would appreciate if someone can reference the thread. Thanks for the help.

HI. I'm trying to write an SQL statement for the row source of a combo box. What i want is a list of properties, but some have flats. a row could be:
5 the road, flat 1
OR
4 the road
(i.e. doesn't have a flat)

so i want the SQL statement to only write the word flat if there is indeed a flat.
so far I have

	Code:
	SELECT 
Properties.ID, 
(case when Flats.ID is null Then  
Properties.Address & ", " & Properties.[House Number] & " " & Properties.[House Name] & ", " & Properties.[Post Code] 
ELSE 
Properties.Address & ", " & Properties.[House Number] & " " & Properties.[House Name] & ", " & Properties.[Post Code] & ",
Flat " & Flats.[Flat Number] END)

AS Expr1, Flats.ID, 

FROM Properties LEFT JOIN Flats.ID ON Properties.ID = Flats.Address

ORDER BY Properties.Address;

With the above I get syntax error (mising operator) and it highlights the word "WHEN". any ideas?

Hi!

I have those (simplified) tables that are linked with an n:m relation

persons:

#ID#FullName#

trainings:

#ID#startDate#

training_members:

#ID#trainingID#personID

Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:

	Code:
	SELECT persons.ID, persons.FullName FROM persons WHERE persons.ID Not In (SELECT personID from training_members inner join
trainings on training_members.trainingID = trainingsn.ID) ORDER BY persons.FullName;

I also added "me.recalc" to the after_update event of the dropdown list.

It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.

Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...

Thank you in advance!

I have a main form with a subform. On a subform I created a combobox which I want to populate with ClassIDs that match the main forms Individual. I know the criteria in VBA for the above would look something like this:

"SELECT tblClassesAttended.ClassID FROM tblClassesAttended WHERE blClassesAttended.IndividualID=" & Me.Parent!IndividualID

but when I type such statement in the row source of the control it does not work. What is the correct way of refering to the parent form when typing the SQL in the row source?
BTW. I cannot use the main form's name

I have a report that is like a spreadsheet...
See screenshot at: http://www.lnrconsulting.com/temp/ResourceRpt.jpg Here

(I know it looks like Excel... but has to be like this...)

Each cell has a diff equation based upon the data in the tables... (I didnt want to have to build a seperate query view for each cell so I fifgured I could just put a SQL statement in the control source but it keeps erroring on me.... Can I not do this? What options do I have?
(I have tried alot of syntax options....)

Here is one of the SQL Statements I tried to use.

=SELECT Count([tbl_Person.personID]) FROM tbl_Person WHERE (((Now() Between [tbl_Person.StartDate] And [tbl_Person.EndDate]) And [tbl_Person.GroupType]="GWHIS"));

Thanks in advance for your input.

Ron

Hi All

The following code works fine if We write it in Excel/vba but if my frontend is Access and I want to write same type of code so that it passes a SQL statement to word document and then print the mail merge word document letters.


	Code:
	Sub Merge_abc()
strWorkbookName = "J:System.mdb"
With CreateObject("Word.Application").Documents.Add("J:Letter1.doc").MailMerge
  .MainDocumentType = 0
  .destination = 1
  .OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `tblmaster` where Printpoolno='" & Textbox1.Value & "'"
  .Execute
   .Parent.Close 0
 End With
  MsgBox "The letters have been printed off"
 Exit Sub
 End If

I hope anyone can point me in the right direction.

Thanks

I'm having trouble opening an ADO recordset using a union SQL statement as the source. I'm wondering if it will even let you do that. I'm trying to avoid storing the union query in the database and calling it as the source if possible. Of note, I am attempting to open two recordsets with different connections and base tables. The one giving me problems is the second.


	Code:
	   Dim cnnLayout As ADODB.Connection, rstLayout As ADODB.Recordset, _
    cnnPatients As ADODB.Connection, rstPatients As ADODB.Recordset, _
    strSQL As String, booCombinationFound As Boolean, i As Integer

    Set cnnLayout = New ADODB.Connection
    Set cnnPatients = New ADODB.Connection
    
    With cnnLayout
        .Provider = CurrentProject.Connection.Provider
        .ConnectionString = GetBackendPath("tbl_Admin_AssessmentReportLayout")
        .Open
    End With
    
    With cnnPatients
        .Provider = CurrentProject.Connection.Provider
        .ConnectionString = GetBackendPath("tblDiagnostic")
        .Open
    End With
    
    Set rstLayout = New ADODB.Recordset
    Set rstPatients = New ADODB.Recordset
    
    strSQL = "SELECT DiagnosticType, ReportSection, SectionPosition " & _
    "FROM tbl_Admin_AssessmentReportLayout " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " ORDER BY SectionPosition"
    
    rstLayout.Open strSQL, cnnLayout, adOpenDynamic, adLockOptimistic
    
    strSQL = "SELECT tblDiagnostic.DiagType as Diagnostic, " & _
    "tblDiagnostic.ReportSection as Section " & _
    "FROM tblDiagnostic " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " UNION SELECT " & Chr(34) & "Pulmonary Function Tests" & Chr(34) & ", " & _
    Chr(34) & "A. Pulmonary" & Chr(34) & _
    " FROM tblPulmFuncTest " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " UNION SELECT " & Chr(34) & "Cardiac Catheterization" & Chr(34) & ", " & _
    Chr(34) & "B. Cardiac" & Chr(34) & _
    " FROM tblCardiacCath" & _
    " WHERE PatientID=" & Me.txtPatientID

    rstPatients.Open strSQL, cnnPatients, adOpenStatic, adLockReadOnly



Hi All,

This is a bit hard to explain and I've searched for an answer to no avail. I think this might be a 2007 bug.

I have a report with a chart on it. The report is based on a query with criteria derived from a form. The chart also has it's row source (obviously) set to a query but until now hadn't been named ie in the row source line in the properties box there was the long line of sql.

The charts query is a crosstab and linked to the report via master/child settings.

My issue is the report opens fine when I leave it as is with the big long line of sql in the row source but when I simply go to the query editor and do Save As a name, close and try to run the report again it makes the chart blank.

I've tried everything I can think of, there is data in the query when you look at it in the query editor, I'm not getting any error messages, I've tried pasting the long sql back in to the row source...the report works...as soon as I save it ...it fails.

Anyone come across this before? It's driving me mad

Cheers, Rachael

I'm having some great difficulty with an SQL statement which is very long, I'm not sure if I'm using the right characters to continue the SQL statement to the next line :


	Code:
	strsql = "INSERT INTO [Activity Details]" _
& "(Activity_ID, Activity_Name, Club_ID, term, [session teacher], [Number of support staff], [number of sessions], [running
cost], [period/duration], [date of first session], [start time of session], [end time of session] , [jp contribution],
[school contribution], [notes - preview], [notes - review], [notes - revision], [target gender], [traveller status], refugee,
gifted, [ethnicity code], [sen status], [learning area]) VALUES " _
& " ('" & Activity_ID.Value & "','" & Activity_Name.Value & "','" & Club_ID.Value & "','" & Term & "','" & Session_Teacher &
"','" & [Forms]![manage - club]![create_activity_subform]![Number_of_support_staff] & "','" & [Forms]![manage -
club]![create_activity_subform]![Number_of_Sessions] & "','" & [Forms]![manage -
club]![create_activity_subform]![Running_Cost] & "','" & [Forms]![manage - club]![create_activity_subform]![Duration] & "','"
_
& first_session & " ','" & start_time & "','" & end_time & "','" & [Forms]![manage -
club]![create_activity_subform].JP_Contribution.Value & "','" & [Forms]![manage -
club]![create_activity_subform].School_Contribution.Value & "','" & [Forms]![manage -
club]![create_activity_subform].Notes_Preview.Value & "','" & [Forms]![manage -
club]![create_activity_subform].Notes_Review.Value & "','" & [Forms]![manage -
club]![create_activity_subform].Notes_Revision.Value & "','" _
& [Forms]![manage - club]![create_activity_subform].Target_Gender.Value & " ','" & [Forms]![manage -
club]![create_activity_subform].Traveller_Status.Value & "','" & [Forms]![manage -
club]![create_activity_subform].Refugee.Value & "','" & [Forms]![manage - club]![create_activity_subform].Gifted.Value &
"','" & [Forms]![manage - club]![create_activity_subform].Ethnicity_Code.Value & "','" & [Forms]![manage -
club]![create_activity_subform].SEN_Status.Value & "','" & [Forms]![manage -
club]![create_activity_subform].Learning_Area.Value & "')"

At the end of each line should be :

" _

And the beginning of the next line should be :

& "

is that correct? Did I miss something in my code? Sorry, its 3am in the morning and I can't seem to think straight.

Actually i need to count the image number by using sql statement and the value get from that statement need to assign variable named imageCount.
Here is code i write but didn't get output.So can anybody plz help me what is wrong with this code:

Sub countImage()
Dim cnn
Dim rs
Dim strSql As String
Dim imageCount As Integer

On Error Resume Next
strSql = "SELECT COUNT(*) AS 'counter' FROM IMAGES WHERE ([IMAGES].[IMAGE TYPE]='RADIOLOGY IMAGES' AND IMAGES.[PAT #]=" & Me![PAT #] & ");"
Set cnn = CurrentDb
Set rs = cnn.OpenRecordset(strSql)
With rs
If Not .EOF Then
imageCount = !counter(Note:I think this part is wrong need attention here)
End If
.CLOSE
End With
Set rs = Nothing
End Sub


Please reply me ass soon as possible.I really appreciate if sombody solve my problem.

Thank You..

I have an NEC MobilePro 790 running Windows HandHeld PC 2000 . It has Pocket Access included in it's ROM. I have a knowledge base on my desktop PC in MS Access. If I query the Access database with the following SQL statement (the name of the table is "searchkb") :

Select * FROM "searchkb" WHERE searchkb.Keywords Like "*Lotus Notes*"

...the database then returns several rows of results from the knowledge base .

I have exported/imported the access database into a .cdb file for pocket access. I've loaded it onto a Compact Flash MicroDrive in the MobilePro and all of the data is visible in the table view of pocket access . All of the datatypes are the same (text), and the Keywords field is indexed.

However, when I perform the exact same SQL query as the statement I listed above, no results are returned . It just returns a single blank row, and no errors are displayed. I tried many variations of SQL statements, and it appears the "Like" command keyword is not supported in pocket access, even though no errors are returned . If I write SQL statements with "is" instead of "like" they return results, but "is" statements only work for exact match criteria. The whole point of the knowledgebase is to be able to perform wildcard searches when you're not sure what you're looking for, so "is" statements are of no use.

I also tried moving the database into main memory and verified the data I'm searching for does actually exist in the pocket access version of the database .

Is it true that "like" and wildcard searches are not supported by Pocket Access? Any ideas, either way? I'm looking for a "free" wildcard search solution for data that is locally stored on the device. Any help would be appreciated.

Help!
I posted this problem last week without the code behind my question, so i am trying again with an example this week.

I am trying to use an SQL statement that builds a recordset in memory, then perform various lookups etc to either update that recordset or build another, then use another SQL statement to drive a listbox to display the results.

Code that i have so far works fine, the list box "LstCompletes" being driven by "cSummary" which is the basic statement taking data from a query named "QryCommissionCompletesDetail" as follows...

Private Sub cmdCompletesDetail_Click()
Dim cSummary As String

cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission ), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"

Me.LstCompletes.RowSource = cSummary

End Sub



BUT!...because i have to sum some of the information as well as perform various lookups to tables for some values required, what i am looking to do is to use the first statement to create a recordset, then play with it, then use a second statement based on the information in this recordset to act as the row source for the listbox.

Private Sub cmdCompletesDetail_Click()
Dim cSummary As String
Dim rstData As DAO.Recordset
Dim cResult As String

cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission ), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"

'Set rstData = CurrentDb.OpenRecordset(cSummary, dbOpenSnapshot)

'cResult = "SELECT * FROM rstData;"

Me.LstCompletes.RowSource = cSummary

'Set rstData = Nothing
'rstData.Close

End Sub


Is there something obvious that i am missing?
As long as i can get the principle correct, using "cResult" driven by "rstData" to act as the rowsource for the listbox, i'm sure i can fumble my way through the rest of what i want to do.


Thanks
Vince

Hi, not much experience in writing SQL statements. These two statements work fine in seperate queries but I need them to work in one query. Can they be put together? I appreciate the help. Thank you.

WHERE ((([Bid Information].State)=[Forms]![Dashboard].[txtEnterState1] Or ([Bid Information].State)=[Forms]![Dashboard].[txtEnterState2] Or ([Bid Information].State)=[Forms]![Dashboard].[txtEnterState3] Or ([Bid Information].State)=[Forms]![Dashboard].[txtEnterState4]))


WHERE ((([Bid Information].[Low Bidder]) Like "*" & [Forms]![Dashboard].[txtEnterVendor] & "*")) OR ((([Bid Information].[2nd Bidder]) Like "*" & [Forms]![Dashboard].[txtEnterVendor] & "*")) OR ((([Bid Information].[3rd Bidder]) Like "*" & [Forms]![Dashboard].[txtEnterVendor] & "*"))

Probably obvious but just in case - "State" is in one field. "Low Bidder", "2nd Bidder" and "3rd Bidder" are all seperate fields in unique records. When we choose the states (up to 4) and a vendor, we need to see the records where that vendor occured, whether in 1st, 2nd or 3rd place AND the other vendors that are in that record.

Example:

I want to see bid results in IN, IL, NY and OH where Vendor ABC bid. If they were second, who was first and who was third?

When I put the two statements in one query, all the states to come through so that's where I'm stuck. I can do some VB (not much but enough to get by on), if that's a better option.

I thank you for your time.

Hello,

Now I admit my skills in VBA are novice, but I can learn fast. I know the basics, but this is beyond me right now.

We are trying to make a process more effecient, and we have queries that require us to first get a total dollar amount and then the total number of records for those dollar amounts.

We then need to get a list of the top 50 records, and put those into an excel sheet.

My first goal is to get the total count of records in the query into a summary form without running this query, but based upon the criteria in the query. I want to do this using VBA.

Query SQL Statement bellow

SELECT Daily_file_FL.HI, Sum(Daily_file_FL.[Submitted Amount]) AS [SumOfSubmitted Amount]
FROM Daily_file_FL
GROUP BY Daily_file_FL.HI
ORDER BY Sum(Daily_file_FL.[Submitted Amount]) DESC;

I have a summary form with unbound text boxes that will populate OnOpen, once I have the VBA correct.

I have played around with Dcount from the table Daily_file_FL but I'm not sure if I can use a SQL statement in the criteria section, which would be awesome. I have no experience writing SQL statements in VBA.

All help is apprecieated. If more info needed please let me know!

I would like to populate cboAssigned with the UserFullName value taken from a SELECT statement. I'm trying to populate the combo box so only assigned records are filtered on a form when a user opens the database. Do I run the SQL statement first or can i just run it directly to the code. I'm not sure how to code this though. Thanks.


	Code:
	      Me.cboAssigned = "SELECT tblUsers.UserFullName " & _
               "FROM tblUsers " & _
               "WHERE tblUsers.UserName='" & fOSUserName & "' "



Hi

I have a form with a box on it where a user can type something, called searchbox.

I want to send a sql statement to a list box on another form, which will look up the name of a site, based on the text typed in the searchbox. I want to use the like statement, so it will search for the word in any part of the site name.

The sql text I have is:

sqltext = "SELECT Q_Search_All.RFC_Number, Q_Search_All.Version_Number, Q_Search_All.Date_Recd, Q_Search_All.Record_Number, Q_Search_All.IA_Status_Narr, Q_Search_All.Site_Name WHERE Q_Search_All.Site_Name like *" & Me.SearchBox & "* FROM Q_Search_All ORDER BY [RFC_Number], [Version_Number]; "

But it finds no records, even though loads of sites exist where the letters exist. I'm guessing I havent written the syntax right in the sql statement around the like clause.... any ideas what I have done wrong?

Thanks
Gary

Hi

I have a survey of 10 questions stored in MS-Access table Table1 with 11 columns

PersonName Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

All questions are multipe choice questions. For some questions the user should choose Yes or NO. However he has to choose amonge 5 answers from 1 to 5

I need to write sql statement the qet me how many Yes or Nos are given to each of question of the first type. And it should gives me for the rest of questions how many choose 1, 2, 3, 4 or 5

We can consider Yes as 1 and NO as 2

for example I expect te result of the query to be someting looks like

Question No (1) (2) (3) (4) (5)

Q1 5 6

Q2 7 3 12 22 1

How to write sql statement qives me that

I appreciate your help

hi,

i have written an sql statement to display fields from a table. but i want it to do this based on a date range (the user enters a parameter)

this can happen in access, but im using vb6 to do this and i dont know how to specify the parameter to wait for user input to filter the results

pls help

I have a Recent Records combo box where the row source is updated every time the record is changed. I want to keep the row source text as it is at the end of the session - when the form is closed. I have tried all sorts of saving methods in form view and design view and am getting nowhere. Usually it doesnt save at all, sometimes it saves row source text from a previous session! Does anyone know how to do this please?

I have one query, where I added the leading "0's" and the SQL statement I used to do this was:

SELECT =Format([SKP B],"00000") AS Expr1
FROM Sheet1;

Then from this query, I want to combine the SKP B (with the leading zero's) and SKP A into one row in Access, and use the following SQL statement to do that:

SELECT [Field1] & "-" & [Field2] AS Field1_Field2
FROM Sheet1;

The problem that I run into is that when I run the 2nd SQL statement the leading "O's" are dropped.

I know that you can do this in two separate queries but I would much rather do it in one if that is possible.
So my question is, is there a way to run two SQL statements in one query?
OR is there a way for me to combine both of these statements into one SQL query?

I have an sql statement in access to find records > 1

3 fields ID, name, and Date.
I use this statement to find if duplicate records were added.
SELECT ID,COUNT(name)AS CNT
FROM Table1
WHERE (((Table1.Date)=[Enter Date]
GROUP BY ID
HAVING COUNT(name)>1;

now I want to find records < 1 ( records not added)
there are 30 records added each week- is there anyway to search and find if any were not entered - without physically looking at report.
thank you


Not finding an answer? Try a Google search.