I really need help on this Query 1

[rry2k]

I have a query that runs correctly but when I run a form with this query as the source I get no records. The query prompts me for the forms_timecardID and then Forms_combowkend which is a date. I have a feeling that it's not interpreting the comparison correctly when I enter on the form. I checked the combobox for isdate and it is. The field in the table is a shortdate. This might be where the problem lies but I can't get a handle on it.

Query:
SELECT DISTINCTROW Time Card Hours.*, [Time Card Hours].[BillingRate]*[BillableHours] AS [Hourly Billings]
FROM [Time Cards] INNER JOIN [Time Card Hours] ON [Time Cards].[TimeCardID]=[Time Card Hours].[TimeCardID]
WHERE [Time Cards].[TimeCardID]=[Forms]![TimeCardsNew]![TimeCardID] And [time card hours].[Weekending]=[forms]![TimeCardsnew]![combowkend];


I really appreciate the help..Russ

 

[jebry]

Hi Russ!

Quick question, is the record source of the form a name of a stored query or the SQL you put in above? Sometimes Access gets picky when you use SQL as the record source, but a stored query shouldn't cause this problem.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[rry2k]

Jeff,

This is a stored query.

Russ

 

[RickSpr]

"The field in the table is a shortdate"--this is a red herring; don't worry about it. Dates stored in tables have no format, in the sense of "long date" or "short date". They're just datetimes, which are always full date and time values. The formats are applied only when they're being displayed.

Using Access 2000, I set up a simple form with a text box, then created a query that accesses the text box in its criteria, then created another form with its record source set to the query name. It worked fine.

Are you using Access 97? Is the TimeCardsNew form open and its fields filled in when you open the new form?

I notice you retyped the query instead of copying and pasting it. (You forgot the brackets around the first occurrence of "Time Card Hours".) It's always a good idea to use cut and paste when posting your code. If you made a typo, we can catch it if you cut and paste, but you might unwittingly correct it if you retype it. Rick Sprague

 

[rry2k]

Rick,

I'm using Access 2000 and actually I did do a cut and paste, I hate typing. The form is blank until I select a name which gives me the timecardid and then I select a date from the combobox which should then provide both sets of criteria for the query.

Thanks I apprecite your time on this..Russ

 

[RickSpr]

The syntax "SELECT DISTINCTROW Time Card Hours.*" should give you a SQL syntax error, then. Names containing spaces (or most special characters) have to be put in "[]" brackets. Could that somehow be related to your problem?

The question comes to mind whether you are entering the parameters into the first form with the second form already open. That wouldn't work without extra code. The second form's Record Source (the query) gets opened when the form is opened, and changing the parameters afterward wouldn't have any effect on it. To make it work, you need to force the second form to reopen its record source, which you can do by calling its Requery method. You would do this after the combo box has been updated (in its AfterUpdate event). For example:

Private Sub Combo1_AfterUpdate
    Forms![Form2].Requery
End Sub

Note that if you're using the combo box's AfterUpdate to do an OpenForm for the second form, it will only work if the second form is closed. If you issue OpenForm for a form that's already open, it doesn't reopen and doesn't go through the normal initialization logic that opens the record source.

I'm clutching at straws here. You probably knew all this already, as you seem to know what you're doing. Besides, what you said about the query asking for parameters doesn't fit my speculation. I just don't see anything right now that explains why it works for me and not for you. Rick Sprague

 

[rry2k]

Rick,
Yes I do know what I'm doing but If I had all the answers I wouldn't be here so I do appreciate any help that you can give. I'm clutching at straws my self here. Let me see if I can clarify this a bit more and maybe something will come to you or someone else. The form I am using is a form with a subform. The main form has 2 comboboxes. cmb1 prompts the user to select a name. cmb(column1) gives me the timecardid then they have to select a weekending date from cmb2. When I just use where [Time Cards].[TimeCardID]=[Forms]![TimeCardsNew]![TimeCardID] it works fine. But as soon as I add : And [time card hours].[Weekending]=[forms]![TimeCardsnew]![combowkend]; The result is empty

 

[RickSpr]

So what I've been calling the "second form" is really a subform? Oh, ok. You must already be doing a Requery on it in combowkend's AfterUpdate, right?

What is the row source for combowkend, and which is the bound column?

Weekending is a datetime, right? Look at the underlying table (Time Card Hours) and make sure the dates have the expected year.

Have you tried making the query "...CDate([combowkend])"?

Is it possible that dates in the table are being entered in a different sequence from what you're entering in the combo box (dd/mm/yy vs. mm/dd/yy vs. yy/mm/dd)? In the early months of a year, and even more so in the early years of a century, short-form dates can confuse Access. It's usually not a problem if all users have the same regional settings for date in the control panel, but it's something to check. Rick Sprague

 

[rry2k]

Rick,
You get the star for the week in my book. You started with the question: You must already be doing a Requery on it in combowkend's AfterUpdate, right?
Wrong!! That was the problem and solution, I requeried the subform but not the combobox.

Thanks alot..Russ

 

[RickSpr]

You're very welcome. Glad I could help...finally. Rick Sprague