Query using table criteria? 3

[jontout]

Hi folks,
I've been wracking my brain to see if I can work a solution into a problem I've been tasked with and so far haven't come up with much.

I've got a simple form, one free text input and an output along with a button to fire off a query and return the answer.
The table I'm using contains financial companies and an allocation for upto 199 consultants per company. The user enters a number between a range and the company name should appear in the output box - here's the table...

Name sRange eRange
Essential Health 20000 20199
Tenon Financial 20200 20399
Protection Plus 20400 20599

So I need to be able to enter 20011 in the input, press a button and have Essential Health returned, this is a small example, I have over 50 companies and thought that having two columns with the 'start' and 'end' range numbers the easiest to manage - I could be wrong!

Is there a way to do a SQL Select query using this method?

Thanks in advance,

Jon

 

[Remou]

How about:

SELECT Company FROM TheTable
WHERE [Enter RangeNumber: ] BETWEEN sRange AND eRange

 

[PHV]

I'd use a ListBox for the output box.
Its RowSource would be something like this (SQL code):
SELECT Name FROM yourTable WHERE [Forms]![Name of form]![name of text input] BETWEEN sRange AND eRange

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

If you're storing data about the consultants, eg, names etc then the best thing would be to have two tables:

tblCompanies
CompanyID - Autonumber (Primary key)
CompanyName - Text
CompanyAddress - text
CompanyTelephone - text
etc

tblConsultants
ConsultantID - Autonumber (PK)
CompanyID - Long integer (Foreign key, linked to tblCompanies.CompanyID)
ConsultantName - text
etc

This means that any number of consultants can be added and are "automatically" associated with a company.

Using your current schema you could do something like

SELECT companyname
FROM YourTable
WHERE (((Yourtable.sRange)<forms!YourForm!YourValue) AND ((Yourtable.eRange)>forms!YourForm!YourValue))

JB

 

[jontout]

Thanks for the tips so far.

I'd got to about this point yesterday and hit a wall.
Thanks to PHV for pointing in the direction of a Listbox, I usually prefer this method in this type of problem - however the stumbling block appears to be, how to pick up the start and end range within the SQL query?
msgboxing the sql string doesn't really add any merit when debugging as the vba snippet I'm using doesn't error.

 

[Remou]

Please post your code.

 

[jontout]

I've been for a smoke and had a bit of an epihany. With a combination of the posts here and what I'd 'achieved' yesterday - we appear to be onto a winner.

I'd put an apostrophe either side of the variable which I think turns the number into a string!

Thanks to PHV & Remou for their useful pointers.
JBinQLD - a great idea which I'll probably incorporate in a future project.

Cheers,

Jon

Here's the snippet of code I'm using, hope someone else finds it useful.

Dim rFind, MySql
rFind = CInt(Text0.Value)

Set db = CurrentDb

MySql = "SELECT Name FROM tblCompany WHERE " & rFind & " BETWEEN (tblCompany.fRange) AND (tblCompany.tRange);"

Text2.RowSource = MySql
Text2.Requery

Now to correctly rename the fields...!

 

[JBinQLD]

msgboxing the sql string doesn't really add any merit when debugging as the vba snippet I'm using doesn't error.

You can always always allow debugging, set a break point (click in the left grey margin) and then exeute your vba snippet. When the code stops press F8 to step through and hover your mouse over any variable or use the immediate window to query any form reference to ensure it has the correct values.

HTH,

JB

 

[JBinQLD]

Sorry, crossover! Glad you got it sorted. And sorry if the debugging idea was way below you, it's often difficult to guage peoples level so I prefer to mention the obvious, but hey ho, someone in fuure may "discover" it here!

JB

 

[jontout]

no worries JB, I do that as well ;-)