Re: Query Problems
From: MGFoster (me_at_privacy.com)
Date: 04/29/04
- Next message: MGFoster: "Re: Link table alternative"
- Previous message: Ted Allen: "Need help to remove tbas, carriage retrun, and other!!!"
- In reply to: James: "Re: Query Problems"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 20:16:13 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The correct PARAMETERS syntax is:
PARAMETERS [Forms]![frmqry]![cboGender] Long ,
[Forms]![frmqry]![cboQuestion] Long ,
[Forms]![frmqry]![cboAnswer] Bit;
An less complex ORDER BY syntax can be:
ORDER BY 2
2 is the 2nd column in the SELECT clause. This can be used instead of
the complex expression used to create the column 2 value.
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQJFikIechKqOuFEgEQJIDACg8KZ/1cRZjwphd1Qk43vcqHMEXDcAn36B xOUHc4dB5D2WpFCuSb04Ga+p =a8K4 -----END PGP SIGNATURE----- James wrote: > Hi I have done the following: > > ----------------------------------------------------------- > > PARAMETERS [Forms]![frmqry]![cboGender] Number ( Long > Integer ), [Forms]![frmqry]![cboQuestion] Number ( Long > Integer ), [Forms]![frmqry]![cboAnswer] Yes/No > (True/False ); > SELECT TOP 1 Count(1) AS ReturnCount, 100*Count(1)/(Select > Count (*) FROM tblResponses) AS ReturnPercentage > FROM tblResponses > WHERE ((([Gender])=[Forms]![frmqry]![cboGender]) AND > (([Question])=[Forms]![frmqry]![cboQuestion]) AND > ((tblResponses.Answer)=[Forms]![frmqry]![cboAnswer])) > ORDER BY 100*Count(1)/(Select Count (*) FROM tblResponses); > > ----------------------------------------------------------- > > Is this correct or do I need to change anything?? as its > telling me the syntex is wrong the the Parameters bit... > > Many Thanks > > James > > >>-----Original Message----- >>-----BEGIN PGP SIGNED MESSAGE----- >>Hash: SHA1 >> >>If the table shows a numeric value in the column that is > > what is > >>important. Open the table in design view and read the > > data type for > >>each of the pertinent columns (put the cursor on the Data > > Type column of > >>the column (Field Name) and in the Field Properties > > section, below, read > >>the Field Size value (one of Integer, Long Integer, > > Double, Single, > >>Byte, Replication ID [for replication only] and Decimal > > [in A2K & > > >>only]). Whatever appears in the Field Size property put > > as the data > >>type of the associated query parameter (use Long for Long > > Integer). > >>Read the Access Help articles on data types for more info. >> >>The ComboBox RowSource should have a query or an SQL > > statement that has > >>both the ID column (usually the number) and the > > description (the text > >>part). The ComboBox's BoundColumn property should be for > > the ID, or > >>numeric, column. This usually means that the text value > > shows in the > >>ComboBox and the numeric value is stored in the table. > > Read the Access > >>Help article on Combo Boxes for more info. >> >>-- >>MGFoster:::mgf00 <at> earthlink <decimal-point> net >>Oakland, CA (USA) >> >>-----BEGIN PGP SIGNATURE----- >>Version: PGP for Personal Privacy 5.0 >>Charset: noconv >> >>iQA/AwUBQJBCl4echKqOuFEgEQKUhgCg2fsa/8g7jMriCOvNjeBGkKJRw2 > > cAoLjJ > >>E/mWwHFaW/ggJ/ATSJ741yn4 >>=55yF >>-----END PGP SIGNATURE----- >> >> >>James wrote: >> >> >>>I or the user sees text but when I look at the tables > > they > >>>are a number value.... >>> >>>How do I get it to look at a number value? but still > > have > >>>the cbobox producing the text rather than a number? >>> >>>Many Thanks >>> >>>James >>> >>> >>> >>>>-----Original Message----- >>>>-----BEGIN PGP SIGNED MESSAGE----- >>>>Hash: SHA1 >>>> >>>>You might try adding the PARAMETERS clause: >>>> >>>>PARAMETERS [Forms]![frmqry]![cboGender] Text, >>>>[Forms]![frmqry]![cboQuestion] Text, >>>>[Forms]![frmqry]![cboAnswer] Text; >>>>SELECT Count(1) AS ReturnCount, 100*Count(1)/(Select > > Count > >>>>(*) FROM tblResponses) AS ReturnPercentage >>> >>>>FROM tblResponses >>> >>> >>>>WHERE Gender = [Forms]![frmqry]![cboGender] >>>> And Question = [Forms]![frmqry]![cboQuestion] >>>> And Answer = [Forms]![frmqry]![cboAnswer] >>>> >>>>The above assumes the data types of each parameter is >>> >>>TEXT. Change them >>> >>> >>>>to the appropriate data type if necessary. >>>> >>>>Of course, you know that the form "frmQry" has to be > > open > >>>and the >>> >>> >>>>controls have to have valid values (not null). >>>> >>>>-- >>>>MGFoster:::mgf00 <at> earthlink <decimal-point> net >>>>Oakland, CA (USA) >>>> >>>>-----BEGIN PGP SIGNATURE----- >>>>Version: PGP for Personal Privacy 5.0 >>>>Charset: noconv >>>> >>>>iQA/AwUBQI641IechKqOuFEgEQJC0gCfUPnHmo/rfz5JTqve9j41IHng > > Hf > >>>0AoJV0 >>> >>> >>>>lKCJD+paDuVnSFNlqKPMmLFK >>>>=4qv7 >>>>-----END PGP SIGNATURE----- >>>> >>>> >>>>James wrote: >>>> >>>> >>>> >>>>>Hello there I have the following SQL.... >>>>> >>>>>------------------------------------------------------- > > - > >>>--- >>> >>> >>>>>SELECT Count(1) AS ReturnCount, 100*Count(1)/(Select >>> >>>Count >>> >>> >>>>>(*) FROM tblResponses) AS ReturnPercentage >>>> >>>>>FROM tblResponses >>>> >>>>>WHERE ((([Gender])=[Forms]![frmqry]![cboGender]) And >>>>>(([Question])=[Forms]![frmqry]![cboQuestion]) And >>>>>(([tblResponses].[Answer])=[Forms]![frmqry]! >>> >>>[cboAnswer])); >>> >>> >>>>>------------------------------------------------------- > > - > >>>--- >>> >>> >>>>>Where by when I run the query from the form with > > values > >>>in >>> >>> >>>>>the cboboxes it asks me to put in the Gender and the >>>>>Question again does anyone know why this is? >>>>> >>>>>Also I have tried the following code: >>>>> >>>>>------------------------------------------------------- > > - > >>>--- >>> >>> >>>>>SELECT Count(1) AS ReturnCount, 100*Count(1)/(Select >>> >>>Count >>> >>> >>>>>(*) FROM tblResponses) AS ReturnPercentage >>>> >>>>>FROM tblResponses >>>> >>>>>WHERE ((([tblResponses].Gender)=[Forms]![frmqry]! >>>>>[cboGender]) And (([tblResponses].Question)=[Forms]! >>>>>[frmqry]![cboQuestion]) And (([tblResponses].[Answer])= >>>>>[Forms]![frmqry]![cboAnswer])); >>>>> >>>>>------------------------------------------------------- > > - > >>>--- >>> >>> >>>>>And I still get the same problem. How can I get it to >>>>>recognise the values? When I type in the values which >>> >>>are >>> >>> >>>>>correct to the table I get no results returened even >>>>>thought there is data in the table. >>>>> >>>>>Many Thanks or any assistence given >>>>> >>>>>James >>>>> >>>> >>>>. >>>> >> >>. >>
- Next message: MGFoster: "Re: Link table alternative"
- Previous message: Ted Allen: "Need help to remove tbas, carriage retrun, and other!!!"
- In reply to: James: "Re: Query Problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
