Date Query

[Nick70]

Hello,
Just found this forum today after beating myself over the head. I'm trying to put together a simple project tracking db. I've been able to handle it so far exept one issue. If I query for projects due on a certain date it brings up all projects for that month instead of just the day specified.

I've attached the db to see if any of you can see what I'm doing wrong.

Thanks

 

[RV]

Your query is incorrect.
Open your query in SQL view and replace your SQL by

SELECT [Projects Data].*, [Project Due Date].*
FROM [Projects Data] INNER JOIN [Project Due Date] ON [Projects Data].ProjectID = [Project Due Date].ProjectID
WHERE [Project Due Date].[Project Due Date] = [Project Due Date?]

Get rid of the Project Due Date table as it's redundant.
Your due dates should be a prpoerty of the Projects Data table.
Also, avoid using spaces in your object names, it will cause mayhem when using VBA

RV

 

[Nick70]

Thanks! I took your advise and got rid of the redundant table. Had to change the SQL code to

SELECT [Projects Data].*
FROM [Projects Data]
WHERE [Projects Data].[Project Due Date]=[Project Due Date?];

And it works great!

This is my first Access attempt so I greatly appriciate the help.

 

[Nick70]

Got a few more question I cant seem to find through a search.

If I need to query for multiple items like due date and project status can I do that with one query?

Also, for exaple the query in the first post. It works fine but is there a way to display it in a form instead of the rows and columns? Reason is that users will have to update due dates and such.

Lastly, if using a Switchboard how can you create a switchboard button to start the query? I did get it to where the switchboard will take you to a form that has a command button to run the query but that seems like an unneeded step.

Thanks for any help.

 

[RV]

If I need to query for multiple items like due date and project status can I do that with one query?

Yes.
Simply add a condition to your query.

Also, for exaple the query in the first post. It works fine but is there a way to display it in a form instead of the rows and columns? Reason is that users will have to update due dates and such.

Create a form where users can input a due date and a status.
Set both text fields to unbound (as in they are not based on a column in an underlying table/query).
Put both fields in the header of your form.

Next, create a query.
Add conditions referring to the text fields on your form.
Let's say, your form is called frmProjectStatus, your due date text field is called txtDueDate, your status text fiels is called txtStatus.

Your query in SQL would read:

SELECT [Projects Data].*
FROM [Projects Data]
WHERE [Projects Data].[Project Due Date] = Forms!frmProjectStatus!txtDueDate
AND [Projects Data].[Status] = Forms!frmProjectStatus!txtStatus

Save your query.
Base your form frmProjectStatus on the query.
Add a button to your form header.
Add an After Update event to your button, add this line of code:

Me.Requery

if using a Switchboard how can you create a switchboard button to start the query?

Create your own switchboard, based on an unbound form.
The standard switchboard is not really convenient

RV