I'm trying to open a form where the value of a field has a date closest to today but not beyond todays date.
I guess i need some sort of MAX function in a where statment maybe????
Any ideas how i could do this.
Thanks.
Ah. The perils of simplifying a question.
We prefer simplified questions of course, but sometimes you leave out a relevant part when trying to simplify. As in this case. Nevertheless we applaud the attempt.
An alternative is now required which selects this whole record, rather than simply the value of the latest date. This now swings right back to the TOP predicate solution - SELECT TOP 1
-
VATRATE,
-
VolunteeDisclosureCOST,
-
VolunteeDisclosureADMIN,
-
VolunteeDisclosureVAT,
-
VolunteerDisclosureTotal,
-
StandardDisclosureCOST,
-
StandardDisclosureADMIN,
-
StandardDisclosureVAT,
-
StandardDisclosureTotal,
-
EnchancedDisclosureCOST,
-
EnchancedDisclosureADMIN,
-
EnchancedDisclosureVAT,
-
EnchancedDisclosureTotal,
-
POVAPOCADisclosureCOST,
-
POVAPOCADisclosureADMIN,
-
POVAPOCADisclosureVAT,
-
POVAPOCADisclosureTotal,
-
EnhancedPOVAPOCADisclosureCOST,
-
EnhancedPOVAPOCADisclosureADMIN,
-
EnhancedPOVAPOCADisclosureVAT,
-
EnhancedPOVAPOCADisclosureTotal,
-
ISARegOnlyCOST,
-
ISARegOnlyADMIN,
-
ISARegOnlyVAT,
-
ISARegOnlyTotal,
-
EnhancedISARegCOST,
-
EnhancedISARegADMIN,
-
EnhancedISARegVAT,
-
EnhancedISARegTOTAL,
-
DateAsOf
-
-
FROM ChargeRates
-
-
WHERE [DateAsOf]<=Date()
-
-
ORDER BY [DateAsOf] DESC
10 5948
The SQL Gang will probably come up with a better solution, but the following SQL Statement will produce the closest Date in the [TheDate] Field in Table 1 to the Current Date, including Today's Date, without going beyond the Current Date: - SELECT TOP 1 Table1.TheDate
-
FROM Table1
-
WHERE DateDiff("d",[TheDate],Date())>=0
-
ORDER BY DateDiff("d",[TheDate],Date());
ADzii's solution should work, but since I don't trust "TOP" to give me a specific record, I would use a query like this: - SELECT MAX(Table1.TheDate) AS MAX_DATE
-
FROM Table1
-
WHERE [TheDate] <= Date();
Also if you use ADzii's code, I cannot stress enough how important the ORDER BY is, you cannot make this work without it.
Good Luck,
-AJ
Your code is better than my code, boy do I hate SQL! (LOL)
Well boy do I love SQL! =P
Yeah [TheDate] <= Date() was my first idea but i thought this will stop once it finds the first date that is <= Date() rather than finding the closest date.
I guess ORDER BY is the way to stop this.
Thanks for you help, i'll give it a go and get back to you.
NeoPa 32,554
Expert Mod 16PB
Not quite Reg. See AJ's post #3 for a pretty perfect solution.
I'm not that familiar with SQL so i'm sure i probably have the syntax wrong somewhere. - SELECT
-
MAX(ChargeRates.DateAsOf) AS MAX_DATE
-
FROM ChargeRates
-
WHERE [DateAsOf]<=Date();
The above works fine, it picks out the closest date to today. Thanks.
When i come to add the other fields needed as below i get the following error when trying to run the query. "You tried to run a query that does not include the specified expression 'VATRATE' as part an aggregate function. I take it that's because the MAX function picks out one record and adding the other fields negates that.
Perhaps i'm going about this all wrong, perhaps i should try to find the record with the closest date though vbcode and then open the form via the PK gained from that record. But i can see that being very long winded and quite possible using arrays to compare dates in the table.
Sorry i'm obviuosly not seeing the obvious, any help would be much appreciated. - SELECT ChargeRates.VATRATE, ChargeRates.VolunteeDisclosureCOST, ChargeRates.VolunteeDisclosureADMIN, ChargeRates.VolunteeDisclosureVAT, ChargeRates.VolunteerDisclosureTotal, ChargeRates.StandardDisclosureCOST, ChargeRates.StandardDisclosureADMIN, ChargeRates.StandardDisclosureVAT, ChargeRates.StandardDisclosureTotal, ChargeRates.EnchancedDisclosureCOST, ChargeRates.EnchancedDisclosureADMIN, ChargeRates.EnchancedDisclosureVAT, ChargeRates.EnchancedDisclosureTotal, ChargeRates.POVAPOCADisclosureCOST, ChargeRates.POVAPOCADisclosureADMIN, ChargeRates.POVAPOCADisclosureVAT, ChargeRates.POVAPOCADisclosureTotal, ChargeRates.EnhancedPOVAPOCADisclosureCOST, ChargeRates.EnhancedPOVAPOCADisclosureADMIN, ChargeRates.EnhancedPOVAPOCADisclosureVAT, ChargeRates.EnhancedPOVAPOCADisclosureTotal, ChargeRates.ISARegOnlyCOST, ChargeRates.ISARegOnlyADMIN, ChargeRates.ISARegOnlyVAT, ChargeRates.ISARegOnlyTotal, ChargeRates.EnhancedISARegCOST, ChargeRates.EnhancedISARegADMIN, ChargeRates.EnhancedISARegVAT, ChargeRates.EnhancedISARegTOTAL,
-
MAX(ChargeRates.DateAsOf) AS MAX_DATE
-
FROM ChargeRates
-
WHERE [DateAsOf]<=Date();
NeoPa 32,554
Expert Mod 16PB
Ah. The perils of simplifying a question.
We prefer simplified questions of course, but sometimes you leave out a relevant part when trying to simplify. As in this case. Nevertheless we applaud the attempt.
An alternative is now required which selects this whole record, rather than simply the value of the latest date. This now swings right back to the TOP predicate solution - SELECT TOP 1
-
VATRATE,
-
VolunteeDisclosureCOST,
-
VolunteeDisclosureADMIN,
-
VolunteeDisclosureVAT,
-
VolunteerDisclosureTotal,
-
StandardDisclosureCOST,
-
StandardDisclosureADMIN,
-
StandardDisclosureVAT,
-
StandardDisclosureTotal,
-
EnchancedDisclosureCOST,
-
EnchancedDisclosureADMIN,
-
EnchancedDisclosureVAT,
-
EnchancedDisclosureTotal,
-
POVAPOCADisclosureCOST,
-
POVAPOCADisclosureADMIN,
-
POVAPOCADisclosureVAT,
-
POVAPOCADisclosureTotal,
-
EnhancedPOVAPOCADisclosureCOST,
-
EnhancedPOVAPOCADisclosureADMIN,
-
EnhancedPOVAPOCADisclosureVAT,
-
EnhancedPOVAPOCADisclosureTotal,
-
ISARegOnlyCOST,
-
ISARegOnlyADMIN,
-
ISARegOnlyVAT,
-
ISARegOnlyTotal,
-
EnhancedISARegCOST,
-
EnhancedISARegADMIN,
-
EnhancedISARegVAT,
-
EnhancedISARegTOTAL,
-
DateAsOf
-
-
FROM ChargeRates
-
-
WHERE [DateAsOf]<=Date()
-
-
ORDER BY [DateAsOf] DESC
Wow perfect, once again you have solved another problem for me, i owe you a drink NeoPa. Although both ADezii and ajalwaysus did give the answer to start with as well. Perhaps i should have been more clearer in my first post, but we got there in the end. Thanks you to all.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tjerk |
last post by:
Hello all,
I have the script below to change an image depending on the date upto
january it worked fine but then it just stopped working does anybody have an
idea how I can make it work again or...
|
by: Eric Terrell |
last post by:
Folks:
I've posted the full source code to C# Programmable Calculator at my
website:
http://www.personalmicrocosms.com/html/cspcalc.html
C# Programmable Calculator is a Reverse Polish...
|
by: Darhl Thomason |
last post by:
I'm converting my Access 2003 VBA app. I have a number of date fields in my
db that I want to use the date/time picker control with, but if there is no
entry in my database, I want the date/time...
|
by: Kun |
last post by:
i have a form which takes in inputs for a mysql query. one of the inputs
is 'date'. normally, a user has to manually enter a date, but i am
wondering if there is a way to create a button which...
|
by: M Skabialka |
last post by:
I am creating my first Visual Studio project, an inventory database. I have
created a form and used written directions to add data from a table to the
form using table adapters, data sets, etc.
...
|
by: irfanali |
last post by:
Hallo All,
This is a Tool i m tryin to develop at work.
I will explain how it works and then the Q
I download a report from my ERP Tool on a daily basis and upload it into the Access Tool....
|
by: GeeItsBee |
last post by:
I am sure this is relatively simple, but I am new to js and would appreciate some help in getting the following sorted.
I use the following in a hidden form field - onsubmit="stamp" which...
|
by: GraemeC |
last post by:
I have a form (single record form) that loads records from a query that are sorted date order as the records are viewed in date order. Some dates are in the past and some are in the future. Currently...
|
by: Chuck Anderson |
last post by:
I run Apache 2.0.55, and Php (both 4.4.1 and 5.2.5) on my home PC
(Windows XP). One of the scripts that I run daily needs to access a
secure URL (https://..............). When I am running Php4,...
|
by: splendid9 |
last post by:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral,...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |