SELECT * FROM tblFuncLog WHERE?

[redbull]

Okay, first off let me say... I am new to using access for access reasons... I have been using it for VBA reasons until now.

What I am ultimately trying to do is create a form, that will run a report or generate an excel file... So I know I need to have a query built to do this first ( I think )

Before I attempt to put what I am trying to do in code let me tell you what tables and columns I am dealing with..

Example
Table Name

• Columns Under Table

tblFuncLog

• chrUser
• chrFunction
• dtmTimeStamp

Analyst_Table

• peregrine_id
• region_uid

So the report I ultimately want to be able to run from a form will logically go like this...

Select * FROM tblFuncLog WHERE chrUser and Peregrine_id are the same (This rules out NLE people) where dtmTimeStamp is between (variable on form?) and (variable on form?) and region_uid is (Variable on form?)


My sql statement is horribly flawed... My problem is I am not sure of the process to achieve this.

Now between the two tables the only thing that remains the same is chrUser & peregrine_id.... I have googled for a bit, however the examples I am finding are not intended for someone as lost as me lol. Once again I appreciate any guidance or advice.

[TheShabz]

SELECT *
FROM tblFuncLog INNER JOIN
Analyst_Table ON tblFuncLog.chrUser = Analyst_Table.Peregrine_id
WHERE
dtmTimeStamp Between #Date1# And #Date2#

Now. the Date syntax will change depending on how you want to store your query. If you are saving a query from the query builder and running it, you would replace the dates with Forms!FormName!ControlName1, one control for each date. If you want to write the SQL out in a string variable in VBA and run it, you would have:
"WHERE dtmTimeStamp Between #" & Me.ControlName1 & "# AND #" & Me.ControlName2 & "#"

EDIT: the chrUser and Peregrine_id must be the same datatype.

[redbull]

THANK YOU!! I am going to try apply this after my PM meetings.

[redbull]

Works beautifully. Quick question though...

I would like to add another filter to this query... based on a form option.

The Analyst_Table has a column named region_uid .. this is either a 1,2 or 3

1 is central - 2 is East - 3 is west

how would you go about adding this filter to the form? if you were doing this, what would you consider the best method?

[TheShabz]

Well, are you looking to add another constraint to the query or filter the main results in a form (subform) view? If the former, add on

AND Analyst_Table.region_uid = Forms!formName!controlName

for design view
or

"WHERE dtmTimeStamp Between #" & Me.ControlName1 & "# AND #" & Me.ControlName2 & "#
AND Analyst_Table.region_uid = " & Me.ControlName

for VBA.

If the latter, you need to change the Me.Filter of the form. Google will help you there.