Search results

Hi, Been away from access for a couple of years and have a new project, I am after a little help bump starting my brain. I have the part numbers for all parts on a range of motorcycles and am looking for a simple example set-up to start from. I have seven types of motorcycle and thousands of...

Many thanks, that was easy. I was being a bit stupid and trying to extract the DataPart from it and then put it back. (y)

Simple question, I have a lot of data with date/time in a field. All the data is for 2020 but I want to change it to 2019. 10/02/20 11:09:53 becomes 10/02/19 11:09:53 10/02/20 13:48:08 Becomes 10/02/19 13:48:08 Effectively a query that looks up all the dates and just changes the year, not the...

That worked perfectly, many thanks.

I have a huge database where the backend is being moved server and it contains lots of hyperlinks in the table fields. For some reason some machines reference the hyperlink to a document as z:/datafiles/data001.pdf and others as //oldserver/datafiles/data001.pdf Technically the hyperlink is...

Many thanks Minty and particularly CJ (for earlier help), is there any benefit to either of these methods over the one I am using?

Hopefully I have sorted it: AND Not (FormatDateTime([Scans]![ScanDate],3))='00:00:00'

Yes, I have that as my query is: Between [Forms]![QMain]![qStartDate] And DateAdd("d",1,[Forms]![QMain]![qEndDate]) This covers all values on a range of days but I want to exclude any values in that range where the time is 00:00:00 (only has date content).

OK, I now have everything working but have to be careful what data to select, my data is a huge data-set that contains both date and time (modern equipment) and only date (old equipment), I am stuck with this as it is used for many functions and editing it is not an option. New: 17/04/20...

Thanks CJ, I now have both parts I need after a little jiggery-pokery, hopefully I can put this to bed now (I will avoid trying to join them together). 😁

No, it's not bad data, just identifying bad practice. If a user puts a part through one test machine and it fails they can then just quickly stick it through another test machine to pass it and print off a pretty certificate saying it has passed, with borderline failures one machine might be...

Beginning to lose the plot now. If I have a table with the following: Scanned: Date & Time SerialNo: Text Field Machine: Text Field 20/02/20 15:25 ABC0002 M0001 20/02/20 16:00 ABC0001 M0001 20/02/20 17:00 ABC0001 M0001 20/02/20 15:25 ABC0002 M0002 20/02/20 16:08...

What I am thinking is make a second query MinofScanned that lists the serial numbers (Scans.Blade) the first time they were scanned using the date and time (Scans.Scanned) and the machine they were inspected on (Scans.Tank) and link this into my other query so that we end up with a list of parts...

Sorry, I am back :unsure: Having abandoned as suggested use of the old timeless data I now have everything working and achieve a list of genuine failures (serial numbers that fail at their last inspection. SELECT LastScan.MaxOfScanned, LastScan.Blade, Scans.Scanned, Scans.Tank, Scans.Co...

and that CJ is the answer, just wish I could work out how to do it. I am happy to leave out all records that have identical dates and times (where the Max should theoretically fail to pass a value) as I am going at this from two directions. I am trying to create a list of failures in previous...

I agree entirely but do not have a solution. I could live with that if Access refused to return a last record on the Max value and had rather hoped it would instead of it cheating and taking a 'generic' last value which is what I assume it is doing. Microsoft I now note state "You can use Min...

OK, getting really confused now, the attached has a set of data on which it cannot return a max value on ABC10003 as there are two entries on the last date of which one passes and the other fails but it still lists it as a failure.

I think I have actually cracked it, many thanks for all the input, it is really useful to bounce things around. If I make: 1. A table of all unique serial numbers 2. A table of all that failed in the last instance 3. A table of all that passed in the last instance I will then have my list of...

Using your earlier example, and I know this is a little crazy, the DMax would not work and presumably fall over. My wild idea was to list the unique serial numbers where the DMax didn't work.

You are absolutely right and I now know the answer, I cannot reliably determine the facts. I am stuck with the data as some of it is twenty years old, modern data does have a correct date and time and I am wondering now if there is a method to run this type of query and in instances where the...