Hi,
I have report being called from a visual basic interface.
Now teh mechanism for it runs fine and as predicted.
The report has the following underlying SQL query controlling it:
SELECT [tblSwimmer].[FirstName], [tblSwimmer].[LastName],
[tblSwimmer].[DateOfBirth], [tblSwimmer].[Sex], [tblSwimmer].[Active],
[tlkpSquad].[SquadName], [Swim].[Distance], [Swim].[SwimMins],
[Swim].[SwimSecs], [Swim].[SwimDate], [Type].[SwimType], [Type].[TypeID],
([SwimMins]*60)+[SwimSecs] AS [Calculated Time], [Stroke].[StrokeName],
[Stroke].[StrokeID]
FROM tblSwimmer, tlkpSquad, Swim, Type, Stroke
WHERE ((([Stroke].[StrokeID])=[Swim].[StrokeID]) And
(([tblSwimmer].[SquadID])=[tlkpSquad].[SquadID]) And
(([tblSwimmer].[SwimmerID])=[Swim].[SwimmerID]) And
(([Type].[TypeID])=[Swim].[SwimType]))
ORDER BY (([SwimMins]*60)+[SwimSecs]);
This brings up loads of qualifying rows.
The visual basic interface passes a filter the typically is of the type:
(Stroke.StrokeID = 1) AND (Swim.Distance = 100) AND (Sex = -1) AND (Active =
0) AND (TypeID <> 3) AND (DateOfBirth BETWEEN #07/03/1992# AND #06/03/1994#)
This is a the filter created by the visual basic to narrow the report to
show data by a set of criteria chosen on screen.
The problem is that by my understanding this particular filter, it should
not return the row that contains the date of birth 09/03/1994. Is there
some SQL query Access issue I dont know about here...
Any suggestions gratefully received as this is driving me nuts...
Many Thanks
R Dutton 1 1910
From your address, I assume you're in the UK. What date format do you use:
dd/mm/yyyy or mm/dd/yyyy? (in other words, is 09/03/1994 the 9th of March or
the 3rd of September?) Access uses mm/dd/yyyy regardless of what your
regional settings are.
You might find it worthwhile to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have at http://members.rogers.com/douglas.j....artAccess.html
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(No private e-mails, please)
"Captain_Drudge" <rd**********@blueyonder.co.uk> wrote in message
news:L7****************@doctor.cableinet.net... Hi, I have report being called from a visual basic interface. Now teh mechanism for it runs fine and as predicted. The report has the following underlying SQL query controlling it:
SELECT [tblSwimmer].[FirstName], [tblSwimmer].[LastName], [tblSwimmer].[DateOfBirth], [tblSwimmer].[Sex], [tblSwimmer].[Active], [tlkpSquad].[SquadName], [Swim].[Distance], [Swim].[SwimMins], [Swim].[SwimSecs], [Swim].[SwimDate], [Type].[SwimType], [Type].[TypeID], ([SwimMins]*60)+[SwimSecs] AS [Calculated Time], [Stroke].[StrokeName], [Stroke].[StrokeID] FROM tblSwimmer, tlkpSquad, Swim, Type, Stroke WHERE ((([Stroke].[StrokeID])=[Swim].[StrokeID]) And (([tblSwimmer].[SquadID])=[tlkpSquad].[SquadID]) And (([tblSwimmer].[SwimmerID])=[Swim].[SwimmerID]) And (([Type].[TypeID])=[Swim].[SwimType])) ORDER BY (([SwimMins]*60)+[SwimSecs]);
This brings up loads of qualifying rows.
The visual basic interface passes a filter the typically is of the type: (Stroke.StrokeID = 1) AND (Swim.Distance = 100) AND (Sex = -1) AND (Active
= 0) AND (TypeID <> 3) AND (DateOfBirth BETWEEN #07/03/1992# AND
#06/03/1994#) This is a the filter created by the visual basic to narrow the report to show data by a set of criteria chosen on screen.
The problem is that by my understanding this particular filter, it should not return the row that contains the date of birth 09/03/1994. Is there some SQL query Access issue I dont know about here... Any suggestions gratefully received as this is driving me nuts... Many Thanks R Dutton
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Laurence Neville |
last post by:
This is regarding a change in the Short Date format under Hebrew Regional
Settings, that has caused huge problems in our ASP web application. The
change appears to have been introduced sometime...
|
by: martini |
last post by:
Need help:
I am trying to call a DB2 stored procedure that requires parameters: 4x int,
date, varchar, int. I use VB6 & oledb.
I'm using statements:
cmd.CommandText = "{call db2admin.proc(1, 0,...
|
by: David B |
last post by:
I have 2 querys with the following in the date criteria
Between DateAdd("m",-6,(!!)) And
(!!)
Between DateAdd("m",-24,(!!)) And
DateAdd("m",-6,(!!))
Problem is they both look at the same...
|
by: Dustin Wilson |
last post by:
I have a problem with the following line of code.
rstOutput! = FormatDateTime("January 1, 2004", vbLongDate)
When I run this on my computer at home it runs without problem but when I
run it at...
|
by: Lyn |
last post by:
Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of birth,
date of death, be in the future, and must not...
|
by: Julie Wardlow |
last post by:
Help!
I am calculating a future date using the DateAdd function in a query (the
calculation also involves an IIf statement), and have managed to get this
formula to produce the required result....
|
by: TN Bella |
last post by:
Hello,
I was looking through the older posts for a solution to this problem,
but I am still confused on how to fix my problem. There are two dates on
my form, one (txtInvDate) is entered by the...
|
by: James P. |
last post by:
Hello there,
In my asp.net page using VB, I have a date text field in mm/dd/yyyy
format. When a date is entered, I'd like to validate it to make sure
the date is greater than or equal to the...
|
by: Jim Armstrong |
last post by:
Hi all -
This problem has been driving me crazy, and I'm hoping the answer is
something stupid I am neglecting to see....
The procedure posted below is part of an Access/SQL database I have...
|
by: creative1 |
last post by:
Hello everyone.
I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this.
The problem is:
I want to print account statement (or any other...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |