How to query in SQL

J

johnh

Hello all,

I need to make a recordset using a SQL string. Problem I'm running
into is I need three fields to be returned and evaluate based on them.
It has to go something like this


Make recordset,
select equipment used,
from that select time and place between a start and end time.

Then I have a module that I call from a report that formats it all into
a single line(compliments of MVPs.org,Thanks Dev!).

Anybody have some code that does something like this?
 
K

Ken Snell [MVP]

Are you sure you need a recordset? Can you do this via the DLookup function,
which can be used in controls' ControlSource expressions, etc.?

If not, post more details about what you need to do; it's not clear what the
recordset is to return, etc.
 
J

johnh

I need to pull three fields from a table-equipment, time and place.

Equipment needs to be a specific part(I have a module for selecting
that part), then it needs to be narrowed down by time which has to be
between a start and stop time listed on a form. The place then gets
printed because it is attached to the time/part record.

I think I need to make it a single recordset because the module I
modified to print it out to the report uses SQL strings to do the
concatenation of field info.

I don't think DLookup can perform these functions.

John Hagenson
 
S

SirPoonga

Are we talking about doing this incode? IE you have a form where you
select an equipment, start time, end time. Click a button and you get
results?
So the code behind the button would generated the report?

If you have a report setup in the code you can set the reports
recordsource and filter (where clause).

Assuming equipment is coming from cboEquipment, start form txtStart,
end from txtEnd, fields names are equipment, time - you might need to
do something like

DoCmd.OpenReport "myreportname", acViewPreview, , "equipment='" &
txtEquipment & "' AND time>=" & txtStart & " AND time<=" & txtEnd

However, if you need a complete SQL statement and don't know how to
write SQL your best bet is to use the query builder to build a query
that does what you want. Then switch it to SQL view and copy and paste.
 
K

Ken Snell [MVP]

DLookup is just a way to do a query that returns a single record; it can
even concatenate the fields' contents into its ouput (in this example,
FieldOne is separated from FieldTwo by a space, and that combination is
separated from Field3 by another space).

MyString = DLookup("[FieldOne] & ' ' & [FieldTwo] & ' ' & [Field3]", _
"MyTableName", "[FieldFilter]=1")

I would be suprised if it cannot do what you seek, but ...

--

Ken Snell
<MS ACCESS MVP>

Post the code that you already have and let's see how we can help you extend
it for what you want. And please include details about the table that
contains your data, what your field names are, and what you exactly want to
find -- will make it much easier to assist you in developing the desired
code.
 
J

johnh

I thought this was posted, but I guess not...

I got the whole thing to work using a couple of simple IF THEN
statements before the Time and Place values got concatenated. Probably
not the best in the west, but it works for my purposes.

Thanks for the replies guys.

John Hagenson
 
J

johnh

I just got it to work. I ended doing a couple of IF THEN statements
BEFORE the date and place values get concatenated into a string. A
little cumbersome and probably not the most efficient way to do it, but
it works!

Thanks for the replies.

John Hagenson
 
Top