Solved

On Load Event handler Question

Posted on 2008-10-10
18
204 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
  • 8
  • 7
  • 2
18 Comments
 
LVL 57
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 59

Expert Comment

by:Kevin Cross
ID: 22691757
What is your database platform and datatype of connsizetype?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 59

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 59

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 59

Accepted Solution

by:
Kevin Cross earned 251 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 59

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 59

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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 249 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 59

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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