Hi all,
I currently have two strings that look like this 070707 and 020709. What i need to do is compare these two dates and see which one is greater.
Is there any easy way to do this since the year is just yy instead of yyyy?
Thanks in advance
Hi, all.
I have string: strSQL = SELECT SUM column FROM table
I need compare it with constant number, for Example:
If strSQL > 50000 Then ....
its not working, any Ideas?
Thank You in advance
A weird question, maybe an impossible one.
Is there a way do something like an intelligent string compare.
What I have is two tables.
I need to find all matching record in both tables based solely on the name.
However it is very common for minor spelling errors and punctuation differences to occur.
e.g. 20,000 instead of 20000 or Name: Whatever instead of Name - Whatever
Is there any way to do a string comparision on this sort of thing and return a liklihood of the strings being the same? Maybe a third party program that can be accessed via VBA? I must be able to control it by VBA as the program needs to be as automated as possible.
I know this may be asking too much but I would appreciate any input or ideas
Thanks
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
This is a lot like my last post, but not exactly...
I have a table with some really strange fields. They all have 4-8 digits, and those digits stand for the date--but not for simple mmddyy. This is much more complicated than that. Each 4 or 2-digit number set stands for something different.
|___START____|_____END____|
|___196303___|___199912___|
|___192603___|___199910___|
|___193101___|___199812___|
|___195606___|___200112___|
|___192801___|___1999_____|
is a sample of the numbers in the fields. Here is the way the website says to define them:
NOTE: The seventh and eighth columns of each document describe the initial and most recent coverage dates using the SICI standard, which defines standard month codes for seasonal and quarterly issues. (A full list of the month codes used for seasonal and quarterly issues appears below.) Dates may appear as a year (1989), a year and month (197601), or a year, month, and day (19331229). Sometimes the format for initial and most recent coverage dates may differ due to changes in publication frequency over the history of the journal. For example, coverage may start with an annual issue (1898) but finish with a monthly issue (199912) or coverage may start with a monthly issue (193002) but finish with a supplemental issue containing no corresponding month (1972).
Month Codes Used in JSTOR Delimited Lists
01=January02=February03=March04=April05=May06=June07=July08=August09=September10=October11=November12=December20=Winter (when it appears at the beginning of a year)21=Spring22=Summer23=Fall24=Winter (when it appears at the end of a year)25=Early Spring26=Late Spring27=Early Fall28=Late Fall29=Early Summer30=Late Summer31=1st quarter32=2nd quarter33=3rd quarter34=4th quarterSo, if it's a 6 digit number, that's going to be 4 for the year, and 2 for one of the codes above.
If it's an 8 digit number, it's going to be 4 for the year, 2 for the month, and 2 for the day.
If it's a 4 digit number, it's just 4 for the year.
There are 665 of these, and I'll need to update them each time I download a new version of the file. So.... can anyone help me? If I could get the numbers into separate cells (at least temporarily), I could do a find and replace and add my final results back together in one cell when it's finished. I would want something that would put the first 4 digits into a cell, then the first 2 digits of what's left into a cell, and then the last two digits of what's left into another cell. I don't mind running 3 different queries.
Thanks,
Siena
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
View 13 Replies View RelatedI have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
Hello all
Ihave tblyear contain ( B_date and E_date )
in anoter form have feald ( invdate ) i wnat ( Invdate) is between B_date and E_date of the tblyear.
Thank u
hi guys/gals!
can u compare date and time in sql?
what i nid is the query showing date and time where time is between 8-530 and date is between (user-defned). it counts how many instances in the datebase occurs during the set date between the said hours.
ex.
date time(8-530)
1/1/2004 30
field name used was [start time] where start time is a date/time data type
ex.
start time
9/20/2004 6:43:47PM
the ff is what i'm using and it doesnt work:
SELECT Calls.[Start Time], COUNT (Calls.[Start Time]) as Exp1
FROM Calls
WHERE Calls.[Start Time] Between [Date1] And [Date2] AND Calls.[Start Time] Between 8:00 AND 17:30
GROUP BY [Start Time];
thanks in advance!
sql1="select * from Exam where StudentID ="+s1+"and dateField='"+dateField+"'";
I am trying to compare the date given in the text field and the date in the table .
I am getting Data type mismatch in criteria expression. Can anyone suggest me a correct query .
Regards
xxx
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
Hi,
I try this way but it doesn't work
SELCT * FROM TABLES WHERE ApplDt > 2007-10-01
which format I have to use.Thanks
Mark
I don't know if anyone else has had this problem when changing from one year to the next, but I haven't found any postings regarding date compare issues. I have a two-fold problem:
1. I have a combo box that lists available dates from a table in Descending order so the most recent date is on top. This worked in the last half of 2006, but when the year changed it is now listing the 2007 dates at the bottom of the list. The field is a Short Date in mm/dd/yyyy format. How do I list them in descending order (01/02/2007, 01/01/2007, 12/31/2006, 12/30/2006...)?
2. The user can also type a date in this field to enter a new record that isn't in the table, but I have After_Update code that also compares the date entered with today's date and gives a message if they enter a future date. This also worked in 2006, but now it is populating the future date message if they enter a date in the past. Here is part of the code:
'*****************************************
Private Sub cmbDate_AfterUpdate()
Dim strDate As String
Dim intResponse As Integer
strToday = Date
If IsNull(cmbDate) Or Len(cmbDate.Value) < 1 Then
MsgBox "Please select or enter a valid date."
cmbDate.SetFocus
Exit Sub
Else
strDate = cmbDate.Value
End If
If strDate > strToday Then
intResponse = MsgBox("You entered a future date. Do you want to continue with this date?", vbYesNo)
If intResponse = vbNo Then
cmbDate.Value = Date
Exit Sub
End If
End If
'**********************************************
I would appreciate any advise.
Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
View 4 Replies View RelatedI have just inherited a database that has dates stored as strings ex.: 01/01/01. I really do not have time to add a new field and convert the string date to date format (this would cause a lot of rewrite to the application that is using this table). I am trying to use the 'between' to display records that are between specific dates. Also, I am using Cold Fusion as the front end, if that makes any difference. Below is an example I thought might work but did not:
<cfquery name="acty_due_month" datasource="customer">
SELECT acty_end_dt
FROM activity
Where DateValue(Format([acty_end_dt], "MM/DD/YY"))=10/30/05
</cfquery>
Any assistance would be greatly appreciated.
The dates in my table are strings that appear as 12305 (1/23/05) and so forth. So some are 5 and some are 6 digits depending on the 1 or 2 digit month. I want to convert them in my query to a date field. I think I have to use the DateSerial and extract the parts of the date, but that leading 0 that is not there is throwing me off. Any easy solutions?
View 2 Replies View RelatedI want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
I am doing a project which currently requires me to input a new file we just got. However, the dates on all of them, as in the field containing the dates, displays something like 20030608 instead of June 8, 2003. I was thinking I could just run the file through a looped vb array to go through the information, reformat it, and then dump it into a new column.
View 2 Replies View RelatedI am trying to create an order ID field based on the 1st 3 letters of the name of the buyer and the date of purchase. For example, if the buyer is James, I want the order ID to become JAM010112 (If the date is 01/01/12). How do I go about doing this?
View 1 Replies View RelatedI have a table with a text field to receive dates or "na" or "n/a". From this, I am trying to run a query that selects all dates that are not "na" and not "n/a" and not null. This works fine with a criteria of
"Is Not Null And Not In ("NA","N/A")"
The problem is, I also must select a date range. In the query, I have created a calculated field equal to various date conversion functions and they all seem to work at converting the text to date. However, I get a Type Mismatch error any time I try to do anything to the date field such as select a date range or to sort on it.
Any suggestions?
Hihi
I have a field containing data in this format
01Apr04:12:34:56
for example
It is not recognized as a date by Access, so it is stored as text
I need to put it in any general date/time format, like
dd/mm/yy hh:nn:ss
I also need that Access actually reads it as a date/time (not a string) to be able to merge with other data, then sort chronogically.
How can I do that (please no code, only query if possible, i'm really a newbie...)
thx!
caroline
In my query I want to extract the last 10 characters of a string in a column which represent a date in the format DD.MM.YY and then convert these to a real date format to be available for further processing.
My query looks like this:
SELECT Angebotskopf.[Laufende Nummer], Angebotskopf.Angebotsnummer, Angebotskopf.Angebotsdatum, Angebotskopf.Anfragedatum, Angebotskopf.Kunde, Angebotskopf.Ansprechpartner, Angebotskopf.Telefonnummer, Angebotskopf.Faxnummer, Angebotskopf.Projekt, Angebotskopf.Preis, CONVERT(varchar(10), RIGHT(Angebotskopf.Projekt, 8),104) AS TestAngebot
FROM Angebotskopf;
But Access gives an error message "unknown function 'CONVERT'"
The "RIGHT" functions works but the resulting column is not being recognized as a date, it is a only a string and therefore useless for processing of any date related calculation.
I have a string value that comes out of a query in the form of
01012015
02012015
12012014
etc...
where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.How do I convert these values to a valid date that can be queried on?
Hi there,
I have to format a date following dd-mmm-yy in an expression also containing text.
Expr1: "Text" & " " Date()
Although separately formatting the date is easily done. I need to keep both int he same expression.
There is a end_date column record with date/time datatype, how could i query datat that showing the particular is empty.
I try end_date = NULL, no record is showing and end_date = ' ' , datatype mismatch... so which one should i write?
In the following statement i tried to send BeginningDate(textbox value) and
EndingDate(txtbox value) to the SQL stmnt but it gave an error saying "Type
Mismatch" even though i used CDate method. answer for this problem is greatly
appreciated......
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND
Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" +
cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND
Region.nRegionId=IndicatorData.nRegionId AND
Institute.nInstituteId=IndicatorData.nInstituteId AND" + _
"IndicatorData.indicDate Between '" + CDate(BeginningDate) +
"' AND '" + CDate(EndingDate) + "' " + _"GROUP BY
IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " "