toadmaster
Last Activity: Feb 2 '11, 08:20 PM
Joined: Aug 13 '09
Location:
-
MMcCarthy and MalcolmK thank you for your help with this matter. I got the code working without having to change the formatting of the date, I had to make some changes to the SQL. I will post my updated code here, just in case someone else is having a similar problem. But thanks you for all your help. -
I also noticed that if I search dates with the least amount of data required to fulfill the criteria it works e.g. if I search between 09/01/2010 and 09/02/2010 the query works correctly however if I search 09/01/2010 and 10/01/2010 then it does not return anything even though it should.Leave a comment:
-
Thank you MMcCarthy for your response
Unfortunately the reason why I had to resort to usingis because my dates in the database are being stored as 22609 (mddyy) and I need them to be displayed as mm/dd/yyyy if I use your suggestion I am getting 2/22/2026 instead...Code:Left(CStr([PDT]),1),Left(CStr([PDT]),2)) & "/" & Left(Right(CStr([PDT]),4),2) & "/" & Right(CStr([PDT]),2)))) AS [AP DATE]
Leave a comment:
-
Date Range Input in Criteria Not Working
Hello,
I would truely appreciate it if I could have some input with this issue.
I have an access query, I am trying to get it to filter the results based on the date input that pops up when the query is run.
Currently when I run it with the relevant dates entered nothing is returned.
...Code:SELECT AP.QN, GI.SN, AP.AT, AP.PE, GI.TR, AP.RP, GI.CT, Max(IIf(Len(NZ([PDT],0))=1,
Last edited by zmbd; Dec 18 '12, 02:10 PM. Reason: [Z{Zombie Resurection: While still alive, stepped the code for better read. Putting it back in the grave now ;) }] -
Neopa cannot thank you enough, I really do appreciate you spending your time to help me out with this. You are awesome.
Thank youLeave a comment:
-
Neopa,
You are awesome, your script does work with the filtering(when used in .mdb), however when I apply to access 2007 which is the format in which the database is in, I keep get the data mismatch error message again. There shouldn't be match difference between the two should there?Leave a comment:
-
Neopa sorry about that, I am using access 2007 so I had to convert it to 2000.
Hope it didn't mess anything up.
Thanks...Leave a comment:
-
Thank you for all your help, please find the database attached
- Within the database is a query called - Delinquency Report. It contains all the SQL and info,
In taking away all the non-relevant information I had to make some changes to the SQL (but it does replicate the error message exactly).
...Code:SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF, PAYD.LTDT, IIf(Len(NZ([LTDT],0))=1,Null,CDate(Format([LTDT],"&&/&&/&&")))
Leave a comment:
-
Neopa - the Exclude against line #22 is a typo on my part(I had to export the result to excel and then typed in the exclude or include).
Yes you are correct all the records are included in the output regardless of the associated value of [DaysDiff]
I am assuming that the field types are text, this an inherited database(also when I right click and look in the property sheet, the format field is blank). I have the option...Leave a comment:
-
Neopa please find the information as requested, thanks again
...Code:DOFD FPYD DaysDiffIndicator 19970808 80897 0 Include 20080515 51508 0 Include 20031214 121403 0 Include 20040501 50104 0 Include 20050805 80505 0 Include 20061205 120506 0 Include 19950728 93194 #Error #Error 19981224 23196 #Error #Error 19980128 23197 #Error #Error 20040501 23004 #Error
Leave a comment:
-
I hope I am not confused, this is the entire code
...Code:SELECT [ACCT] & "-" & [SUFF] AS [ACCT+SUFF], PAYD.ACCT, PAYD.SUFF, PAYD.DOFD, PAYD.FPYD, DateDiff('d',CDate(Format(Nz([FPYD],''),'&&/&&/&&')),CDate(Format(Mid(Nz([DOFD],'99991231'),5,4),'&&/&&/') & Left(Nz([DOFD],'9999'),4))) AS DaysDiff FROM MAIN RIGHT JOIN PAYD ON MAIN.ACCT = PAYD.ACCT WHERE (((PAYD.DOFDLeave a comment:
-
Neopa thanks for your help, I cannot even begin to express how much I appreciate all the effort you are putting into this.
I am not sure if I understand your instruction correctly but in my earliar posting
Toadmaster: NeoPa thank you for your response, my major huddle right now is, it's still displaying 2018 records instead of the 78 records which actually qualify under the criteria (<120).
I tried...Leave a comment:
-
I did try out your SQL, only thing is it is still displaying 2018 records and not the 78 records which qualify under the criteria (<120)
Sorry I didn't make myself clear enough. Thank youLeave a comment:
-
NeoPa thank you for your response, my major huddle right now is, it's still displaying 2018 records instead of the 78 records which actually qualify under the criteria (<120).
I tried to work around it by splitting the code into two different fields DOFD and FYD and then subtracted [DOFD]-[FYD] after which I then enter <120 in the criteria field.
This then throws up the data mismatch error message again.Leave a comment:
-
Adezii and Paradox I really do appreciate all that you are doing to help.
The thing is Adezii with your recommendation, currently my dates are being displayed as 120891(which is mmddyy), I need it to be displayed as mm/dd/yyyy hence reason why I am using
...Code:CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2))
Leave a comment:
-
I have been doing some research and came across this post,
http://bytes.com/topic/access/answer...pression-query
I also stripped out my SQL nailing down the bit where the error message seems to occur as suggested by Neopa(thank you)looks like the Data Mismatch is being caused by Null dates.
I modified my code as suggested in the post above and I am still getting the error...Leave a comment:
-
Thanks ADezii for your quick response, unfortunately I am still getting the same error message after changing the parts of the of the code that show Len(NZ([FPYD])) to
Code:CVDate(IIf(Len([FPYD])=5,Left(CStr([FPYD]),1),Left(CStr([FPYD]),2)) & "/" & Left(Right(CStr([FPYD]),4),2) & "/" & Right(CStr([FPYD]),2)) AS [FIRST PAYMENT DATE]
Leave a comment:
-
Data Type Mismatch - CVDate
I would greatly appreciate it if I could get help on the following code
I keep getting a "Data Type mismatch" when I use the criteria (<120). It works fine when I take off the criteria.
The dates in the database are in different formats i.e. DOFD looks like this 19941208(which I then convert to MM/DD/YYYY) on the other had FPYD is being stored as 120891(which I also then convert to MM/DD/YYYY).
... -
I am sorry if my request came across as demanding. Did not in anyway mean that.
Sorry if I upset you
Thank youLeave a comment:
-
Find my code below
The emailaddress is being captured when the user exists in the manager table; however the emailaddress is not captured when the user only exists in the member table. I need this emailaddress to be captured because in another procedure this emailaddress is used to send an email using this email address (emailaddress value is stored in another table called purchasetable)
...Code:Private Sub purchase_Click() Dim mess_body
Leave a comment:
No activity results to display
Leave a comment: