Search results

Thank you so much, that works a treat! The criteria, what does it actually tell access to do? "[Mat_ID] = '" & [Mat_ID] & "' And [Publish_DateTime] < " & SQLDate([Publish_DateTime]))=0, I cant get my head around the =0 or how adding the Publish_DateTime to the Mat_Id is working? Thanks again,

Hi, So that kind of works, but, it returns the first date in the whole list, not the first date based on the Mat_ID. I have Re-attached the Database, adding in the Mat_ID col. and an "Expected" column for return. Thank you for helping me with this, i am very grateful and learning a lot.

I can convert them to US, apply this and then change back if required? What format do they need to be? mm/dd/yyyy HH:mi:ss??

Hi, thank you for that, I changed the table (DOMAIN) name, but i get the same result for all. My Table is called "Dplay_Ordering_Step_2" I have attached images so you can see my data better. - Date list - Table Heading I changed your code to this: FirstOrNot...

Thanks, So, my Fields are: Mat_ID Publish_DateTime RowNum(although it sounds like you wont want to use that, the table is cleared and repopulated each time before being accessed for information, so all records would be new each time this query is run) TableName is "Dplay_Ordering_Step_2"...

Hi all, I have some data where there is a Product_ID, linked to this is the first appearance of this ID, Ship_Date. Furthermore, i have ordered the list by Ship_Date and autoNumbered the rows 1 > etc.. I need a way of finding the first appearance of that Product_ID and giving it an alias...

They are not, all this time i have only looked at the columns that i am comparing. But when i decided to Append the query to a table the process informed me of 20 cells that were screwed, turns out they were all data-formats on another column where the user had put them in wrong (or as text or...

Thanks, I still cant work it out, its really weird. I built 2 new tables of FAKE data and rebuilt the query and the joins and it works fine. So somewhere is an issue, but i cant work out what. The only difference, in my test one, they were tables not Queries, so i might append a table and run...

Sorry maybe I am confusing things, I only free type on this forum, everything else uses the tools and features in access, I don't write the SQL unless I have too. I have tried all the solutions in this thread, all produce the same result. The syntax is correct the error must be of logic. But...

I know, the sub-select was an effort to and force the null values from Q2. Either way, it's still returning #Error and I have no idea why. Would it be to do with the fact I am joining on the data I am returning? I don't see why, but I am out of ideas now! I really hate access sometimes!

I free typed, its IIF. I can only choose from Inner, Left or Right Joins in the Query builder. With Inner Join it only returns the rows where there is an ID Match. But i need to return the rows with no match and identify them. I thought LEFT would return all the rows in Q1 and mark "No...

I have 2 queries, on each one sits a string column called Package_ID. I have created a LEFT JOIN between the 2, (all Items on Q1 and only matched Items on Q2) My Statement is IFF([Q1].[PackageID] = [Q2].[PackageID], "Match", "NO Match") I expect where the IDs match to return MATCH (This...

Hi CJ Thanks for the code, however, this is part of an end to end workflow, by this i mean the data gets erased at the start of every process. I erase all tables, import a handful of Excel files, run them through bunch of queries to change and the data and add info, then pop them out into new...

Perfect, thank you for pointing me in the right direction. :-)

Hi, I have data that looks something like below, ID | DATE | INFO 111 1/05/2016 Prog A 111 3/05/2016 Prog A 111 2/05/2016 Prog A 222 1/05/2016 Prog B 222 6/05/2016 Prog B 333 2/05/2016 Prog C The ID is NOT Unique, it's a TV Programme ID What...

Right, all my data is pulled through the range of queries as CDATE. Access can see everything as a date type as it offers date filters when you choose filter. BUT, i am still getting told that i have an invalid use of null. There are no null values on any of the quieres, just on the source...

OK, there is an issue with my Date Time concatenation, some values are pulling in as string.. I think i need a query between these to change the source to Date and time.

Thanks, i have re-written to your DATADIFF, which makes more sense. But i still get the error. there are no null values in the queries i am applying the maths too. Is there a way to check that the fields are definitely outputting a "date" value?

Thanks Ranman, There are no null values, which is why i am confused. However, i did notice that on the CDATE formula it outputs 01/01/2016 00:00:00 as just 01/01/2016 and fails to print the time value. I might plus 1 second on it and see if that helps. your NZ solution still failed to the...

Hi, so i have built a query that takes a date cell and a time cell and puts them into a new cell as "dd/mm/yyyy hh:mm:ss" Publish_DT: [Schedule Date] & " " & Format((TimeValue([Schedule Time])+TimeValue([Duration]))-(TimeSerial(0,15,0)),"Long Time") Then i have another query that takes a...