UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open To Specific Date Range, Access 2016    
 
   
mike60smart
post Jan 26 2020, 11:46 AM
Post#1


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am having a problem trying to run a report for a specific Date Range.

This does not work:-

CODE
SELECT tblContractPurchaseOrder.CustomerPurchaseOrderID, tblContractPurchaseOrder.Retainage, tblG703.ApplicationDate, tblG703.ITEMNO, tblG703.PeriodTo, tblG703.DESCRIPTIONOFWORK, tblG703.SCHEDULEDVALUE, tblG703.CHANGEORDERS, tblG703.FROMPREVIOUSAPPLICATION, tblG703.THISPERIOD, tblG703.MATERIALSPRESENTLYSTORED, tblG703.TOTALCOMPLETEDANDSTOREDTODATE, tblG703.PERCENTAGE, tblG703.BALANCETOFINISH, tblG703.RETAINAGE, Val([FROMPREVIOUSAPPLICATION]+[THISPERIOD]+[MATERIALSPRESENTLYSTORED]) AS A, Val(IIf([A]=0,"",[A]/[SCHEDULEDVALUE])) AS H, IIf([H]=0,0,[SCHEDULEDVALUE]-[A]) AS I, [A]*[tblContractPurchaseOrder].[RETAINAGE] AS Ret, tblG703.Distribution
FROM tblContractPurchaseOrder INNER JOIN tblG703 ON tblContractPurchaseOrder.CustomerPurchaseOrderID = tblG703.CustomerPurchaseOrderID
WHERE (((tblG703.PeriodTo) Between [Forms]![frmAIMReports]![txtStartDate] And [Forms]![frmAIMReports]![txtEndDate]));


But this does when I just hard code a specific date:-

CODE
SELECT tblContractPurchaseOrder.CustomerPurchaseOrderID, tblContractPurchaseOrder.Retainage, tblG703.ApplicationDate, tblG703.ITEMNO, tblG703.PeriodTo, tblG703.DESCRIPTIONOFWORK, tblG703.SCHEDULEDVALUE, tblG703.CHANGEORDERS, tblG703.FROMPREVIOUSAPPLICATION, tblG703.THISPERIOD, tblG703.MATERIALSPRESENTLYSTORED, tblG703.TOTALCOMPLETEDANDSTOREDTODATE, tblG703.PERCENTAGE, tblG703.BALANCETOFINISH, tblG703.RETAINAGE, Val([FROMPREVIOUSAPPLICATION]+[THISPERIOD]+[MATERIALSPRESENTLYSTORED]) AS A, Val(IIf([A]=0,"",[A]/[SCHEDULEDVALUE])) AS H, IIf([H]=0,0,[SCHEDULEDVALUE]-[A]) AS I, [A]*[tblContractPurchaseOrder].[RETAINAGE] AS Ret, tblG703.Distribution
FROM tblContractPurchaseOrder INNER JOIN tblG703 ON tblContractPurchaseOrder.CustomerPurchaseOrderID = tblG703.CustomerPurchaseOrderID
WHERE (((tblG703.PeriodTo)=#1/1/2020#));


Any help apprciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Jan 26 2020, 12:02 PM
Post#2



Posts: 6,355
Joined: 11-November 10
From: SoCal, USA


hello Mike, try setting the parameters for the form references. i don't have A2016 at home, but if i recall correctly, there is a dialog accessible from query Design view (not SQL view) to help, so you don't have to write the PARAMETER statement freehand in SQL view. same as in earlier versions of Access. once you set the parameters in the dialog, you can open SQL view and see the PARAMETER statement; it'll be added to the SQL statement, above the SELECT... :)

hth
tina
This post has been edited by tina t: Jan 26 2020, 12:03 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Jan 26 2020, 12:32 PM
Post#3


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi June7

Tried this but get a Syntax Error:-

Attached File  error.PNG ( 24.74K )Number of downloads: 7

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MadPiet
post Jan 26 2020, 12:40 PM
Post#4



Posts: 3,465
Joined: 27-February 09



Normally you'd do something like

WHERE SomeDate >= #" & dtStartDate & "#
AND SomeDate <= #" & dtEndDate & "#"
Go to the top of the page
 
projecttoday
post Jan 26 2020, 12:54 PM
Post#5


UtterAccess VIP
Posts: 11,579
Joined: 10-February 04
From: South Charleston, WV


It should work without #'s. Try this and see what happens:

WHERE (((tblG703.PeriodTo) = [Forms].[frmAIMReports].[txtStartDate]

What is the format of txtStartDate? Is there a time in addition to the date in either PeriodTo or txtStartDate? You may need DateValue.

--------------------
Robert Crouser
Go to the top of the page
 
mike60smart
post Jan 26 2020, 01:00 PM
Post#6


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Robert

The Format of PeriodTo is Short Date

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Jan 26 2020, 01:02 PM
Post#7


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Robert

txtStartDate and txtEndDate are Unbound Controls Format set as Short Date on an Unbound Form


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
projecttoday
post Jan 26 2020, 01:02 PM
Post#8


UtterAccess VIP
Posts: 11,579
Joined: 10-February 04
From: South Charleston, WV


It should work. Did you try it without Between?

What is the format of txtStartDate?

--------------------
Robert Crouser
Go to the top of the page
 
mike60smart
post Jan 26 2020, 01:08 PM
Post#9


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Robert

Tired the following and get the displayed error:-

Attached File  error.PNG ( 29.21K )Number of downloads: 17

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
projecttoday
post Jan 26 2020, 01:08 PM
Post#10


UtterAccess VIP
Posts: 11,579
Joined: 10-February 04
From: South Charleston, WV


I just did a test.

SELECT ShipsLog.Table1ID, ShipsLog.EntryDate
FROM ShipsLog
WHERE (((ShipsLog.EntryDate)=[forms].[form1].[text2]));

returns records for the date in text2 on form1 correctly. It works whether or not text2 is formatted as a short date.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Jan 26 2020, 01:11 PM
Post#11


UtterAccess VIP
Posts: 11,579
Joined: 10-February 04
From: South Charleston, WV


Looks like you're missing a couple of ))'s at the end.

If you can't get it to work with 2 dates try to get it to work with 1 date first.

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post Jan 26 2020, 02:50 PM
Post#12



Posts: 6,355
Joined: 11-November 10
From: SoCal, USA


hi Mike, the PARAMETERS statement is incorrect. that's why i suggested using the dialog instead of typing it freehand. :) i created a query myself (in A2003, but the syntax should be the same) and used the dialog, to get the correct syntax, and then pasted in your form references from your first post. try:

PARAMETERS [Forms]![frmAIMReports]![txtStartDate] DateTime, [Forms]![frmAIMReports]![txtEndDate] DateTime;
SELECT ...

hth
tina

btw, hon, i'm not June7. ;)

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Jan 28 2020, 02:16 AM
Post#13


UtterAccess VIP
Posts: 13,649
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

My apologies for getting you mistaken for someone else.

Your solution appears to work just great

Many thanks
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 28 2020, 05:11 AM
Post#14


UtterAccess Moderator
Posts: 12,253
Joined: 6-December 03
From: Telegraph Hill


Just as a FYI, Mike,

The original syntax error was pointed out by Robert - your WHERE clause had opening brackets which were never closed.

It would have worked as was (without the PARAMETERS clause) if you had changed the last line to:
CODE
WHERE tblG703.PeriodTo BETWEEN [Forms]![frmAIMReports]![txtStartDate] AND [Forms]![frmAIMReports]![txtEndDate];


Access annoyingly always tries to add the extra brackets.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Jan 28 2020, 03:17 PM
Post#15



Posts: 6,355
Joined: 11-November 10
From: SoCal, USA


QUOTE
The original syntax error was pointed out by Robert - your WHERE clause had opening brackets which were never closed.

hi David, i've looked and looked but can't see the syntax error in Mike's originally posted WHERE clause.

QUOTE
WHERE (((tblG703.PeriodTo) Between [Forms]![frmAIMReports]![txtStartDate] And [Forms]![frmAIMReports]![txtEndDate]));

help?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Jan 28 2020, 04:13 PM
Post#16


UtterAccess VIP
Posts: 11,579
Joined: 10-February 04
From: South Charleston, WV


It's in post #9.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2020 - 03:07 PM