Based on what I've seen in other posts, I'd expect the SQL to be something
such as this...
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start) Between CDate([Forms]![Calendar].[IO_Start]) And
CDate([Forms]![Calendar].[IO_End])));
However, everything is swept into the IO_Start Date Filed. Now the start
date is evaluated correctly, but it is still not correct. When I go into
Design View, I see that the IO_Start date has this in the Criteria:
Between CDate([Forms]![Calendar].[IO_Start]) And
CDate([Forms]![Calendar].[IO_End])
The Criteria for the end date is blank.
Almost there...
--
RyGuy
ryguy7272 said:
The RM Table is just a repository for big data dump. Yes, calculations are
done elsewhere. Yes, I am duplicating some info. that could be calculated
outside of the Table. It just happens to be the format for something else;
I'm not doing any calculations in the Table.
I tried the WHERE syntax; still not working. The rows are just blank when I
plug in your WHERE clause. I guess I'll be fiddling with this over the
weekend; hopefully I can get it straightened out before Monday.
Thanks,
Ryan---
--
RyGuy
:
On the surface I don't notice anything wrong with your Where clause.
You might try removing the > and < and just making it equal, then enter
some dates in the form that you know exist in the table, then test to
see if the query returns those records.
WHERE (((RM.IO_Start)=[Forms]![Calendar].[IO_Start]) AND
((RM.IO_End)=[Forms]![Calendar].[IO_End]));
As far as your RM table goes, you have a couple of issues.
First, it appears you are hiding data in your field names. For example,
you have fields for each month of the year. Normally you would just
have a date field (or fields - like StartDate/EndDate) and you would
use a query to extract data by month. You also have fields like
SalesRegion and SalesOffice, etc. that look like they could be data
that belongs in a separate, related table.
Second, it appears you are storing calculated values in your table
(Q1 - Q4 and TOTAL). This is almost never a good idea.
--
_________
Sean Bailey
:
I changed the TextBoxes to this:
[IO_Start] and [IO_End]
That matches the names of the Fields in the Table.
Yes, the Table is named 'RM'. I agree, there is a flaw. I don't know what
it is though, and I don't know how to resolve it. Any ideas?
Thanks,
Ryan---
--
RyGuy
:
What are tha actual names of the text boxes on your form? In your first
post you say they are called [QStart] and [QEnd], but in your second
post you seem to indicate that they are called [IO_Start] and [IO_End].
On a side note, is RM a table or a query? If the former, it would appear
that you have design flaw.
--
_________
Sean Bailey
:
If I try this Criteria in the IO_Start Field:
=[Forms]![Calendar].[IO_Start]
I get some records that don't make a whole lot of sense; seems to be pretty
random. This is the SQL now:
SELECT RM.SalesRegion, RM.SalesOffice, RM.SalesRep, RM.Advertiser,
RM.Advertiser1, RM.Agency, RM.IO, RM.IO_Start, RM.IO_End, RM.inventoryClass,
RM.CUR_MTD_REV, RM.JAN, RM.FEB, RM.MAR, RM.Q1, RM.APR, RM.MAY, RM.JUN, RM.Q2,
RM.JUL, RM.AUG, RM.SEP, RM.Q3, RM.OCT, RM.NOV, RM.DEC, RM.Q4, RM.TOTAL
FROM RM
WHERE (((RM.IO_Start)>=[Forms]![Calendar].[IO_Start]) AND
((RM.IO_End)<=[Forms]![Calendar].[IO_End]));
I guess the parameters are not being passed to the Query. I have this code
under the Form:
Private Sub Command5_Click()
DoCmd.OpenQuery "qryCalendar", acViewNormal
End Sub
There must be a problem with the WHERE...
Just can't tell what it is...
Thanks,
Ryan---
--
RyGuy
:
I have a Form set up, and it is linked to a Calendar. The calendar dates go
into tow TextBoxes just fine, but I can't seem to pass the values from the
TextBoxes to the QBE grid. I tried the following:
[Forms]![Calendar].[QStart] >= #" [QStart] & "#"
[Forms]![Calendar].[QStart] >= #" & CDate(QStart) & "#"
Neither attempt worked. The Form is named Calendar and the TextBoxes are
named QStart and QEnd. What am I doing wrong in the QBE?
Thanks for the help.
Ryan---
