using a text box in a Query works fine with one table but fails when applied to another

1
votes

I have created two text boxes on a form that allow me to select a start and end date of a query. My query works great for one of my tables that has a date column (MM/DD/YYYY). I would like to have it work on table2 that has just the month for date i.e; 1 for Jan, 2 for Feb, 3 for Mar, etc. The Text boxes are formatted as General Date. The reason for the two tables is that table1 has multiple entries per month and table2 sums that entries and gives a monthly value. Here is my SQL that I am trying

SELECT [Property],[Value]
From [Table1]
Where [Date] Between [Forms]![Calendar Test]![Start Date] and [Forms]![Calendar Test]![End Date]

This works great with Table1. Since I have multiple entries per month per property table1 gives each entry and this is more than I want. I would prefer getting the summed value from table2 where there is just 1 entry per property a month.

SELECT [Property],[Value]
From [Table 2]
Where [Month] Between [Forms]![Calendar Test]![Start Date] and [Forms]![Calendar Test]![End Date]

The output from this second query is a blank return where the fields Property and Value are not populated with anything as opposed to the Table1 query where the data populates the fields.Is this because I have the "Month" field set up to give just (MM) instead of (MM/DD/YYYY)? was thinking that it might be an issue with the format of the Text Box calendar since it gives a full date and the table2 Month just has a month value?

1
sqlms-access

1 Answers

0
votes

If the datatype of your [Table 2].[Month] field is numeric, use the Month Function with your text box date values.

Where [Month] Between
        Month([Forms]![Calendar Test]![Start Date])
    and Month([Forms]![Calendar Test]![End Date])

If that field is text datatype, cast it as a number when you compare to the Month() values ...

Where Val([Month]) Between
        Month([Forms]![Calendar Test]![Start Date])
    and Month([Forms]![Calendar Test]![End Date])

Beware, if [Start Date] and [End Date] can be from different years, you will need a different strategy.