Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

On Load Event handler Question

Posted on 2008-10-10
18
Medium Priority
?
224 Views
Last Modified: 2010-04-21
What is the best way to load the following sizes at the "On Current Event Handler"
0.75, 1, 1.5 and 2 from tblConnSize.

tblConnSize
ConnSizeID         ConnSize       ConnSizeType
(Autonumber)        (Text)              (Text)
      1                         5/8                  0.625
      2                         1/2                  0.5
      3                         3/4                 0.75     '<<=== Size to be loaded
      4                          1                    1.0      '<<=== Size to be loaded
      5                        1-1/4               1.25
      6                        1-1/2               1.5       '<<=== Size to be loaded
      7                          2                    2.0      '<<=== Size to be loaded



Note:
Only sizes indicated above are to be loaded.

I tried the following but received a "Data Mismatch Error"

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype '0.75' Or '1' Or '1.5' Or '2' ORDER BY tblConnSize.ConnSizeType; "
cboConnSize.Requery
0
Comment
Question by:billcute
[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
  • 8
  • 7
  • 2
18 Comments
 
LVL 58
ID: 22688838
You need:

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype = '0.75' Or tblconnsize.connsizetype =  '1' Or tblconnsize.connsizetype = '1.5' Or tblconnsize.connsizetype = '2' ORDER BY tblConnSize.ConnSizeType; "
0
 
LVL 4

Author Comment

by:billcute
ID: 22690686
I tested this and received
"Syntax Error (missing operator) in query expression:
'tblconnsize.connsizetype = '0.75' Or tblconnsize.connsizetype =  '1' Or tblconnsize.connsizetype = '1.5' Or tblconnsize.connsizetype = '2'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22691757
What is your database platform and datatype of connsizetype?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22691769
Sorry I see you have above and believe you are using Access.
0
 
LVL 4

Author Comment

by:billcute
ID: 22691772
Ms Access 2003.
datatype of connsizetype = number
Field Size ==>> double
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22691777
Try like this:
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN('0.75','1','1.5','2') ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1004 total points
ID: 22691781
Well glad I asked then, as for number try this:
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.75,1,1.5,2) ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22691788
I missed a space:
IN(0.75,1,1.5,2)

Should be:
IN (0.75,1,1.5,2)
0
 
LVL 4

Author Comment

by:billcute
ID: 22691816
In trying to open the combo (cboMainSize), I received an error message:

"The table tblConnSize is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated."

What do you think is wrong?
0
 
LVL 4

Author Comment

by:billcute
ID: 22691829
mwvisa1:
You were correct the first time. I tried your first posted solution with IN(0.75,1,1.5,2) and it worked.
Thanks
0
 
LVL 4

Author Comment

by:billcute
ID: 22691849
No space is required.
If I may ask you one more question?
What if I want to list the following:
0.625, 0.75
skip...1, 1.25, 1.50 and then....list all sizes greater than 2

this means:
IN(0.625,0.75) Or >2 such that it looked like this:

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.625,0.75); Or >2 ORDER BY tblConnSize.ConnSizeType;"

Is this possible?
 


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22691924
Yes it is possible.
cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where [tblConnSize].[ConnSizeType] IN(0.625,0.75) Or [tblConnSize].[ConnSizeType] > 2 ORDER BY tblConnSize.ConnSizeType;"

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 22692341
mwvisa1:
Thanks for the additional info

Regards
Bill
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 996 total points
ID: 22693627
<<Ms Access 2003.
datatype of connsizetype = number
Field Size ==>> double>>

Your initial post indicated this was text:

"tblConnSize
ConnSizeID         ConnSize       ConnSizeType
(Autonumber)        (Text)              (Text)"

cboConnSize.RowSource = "SELECT [tblConnSize].[ConnSizeID], [tblConnSize].[ConnSize], [tblConnSize].[ConnSizeType] FROM tblConnSize where tblconnsize.connsizetype = .75 Or tblconnsize.connsizetype =  1 Or tblconnsize.connsizetype = 1.5 Or tblconnsize.connsizetype = 2 ORDER BY tblConnSize.ConnSizeType; "

  Would have worked is well, although IN is certainly a lot more elagent.

JimD.
0
 
LVL 4

Author Comment

by:billcute
ID: 22693942
JDettman:
I just realized that youi were right. It was my fault originally.

The original question was 250 points as such I have asked CS to re-open the question and increase the points to 500 in order to be able to split the points.

Regards
Bill
0
 
LVL 4

Author Closing Comment

by:billcute
ID: 31505103
Both answers were correct but accepted mwvisa1' answer - Points split 251 to 249 .
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22694837
Well done, Billcute.  Don't see that happen often, but is very appropriate. :)

Regards,
Kevin
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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