My database buttons no longer work

[mcmcd99]

Hello, my database has stopped working correctly!



Its basic function is to log and filter air traffic for a small general aviation airport.

I have buttons that open reports that filter data with WHERE conditions. It worked for a few weeks, but now they aren't opening any results even though the data is there.
I personally havent changed anything, but I am pretty exhausted from trying to get what should be a simple database to work. I don't know what could have caused the change.

The database file is still small, appx 7megs. Would anyone who is skilled in Access mind taking a look and helping me. It is so close to being done, or so I thought.

[davegri]

Sure, post it here. I'm sure you will get responses.
Bye, the way, have you moved the database to a different folder or different disk drive? If so, Google 'Access trusted locations'.

[mcmcd99]

There we go, it's uploaded. Crossing fingers!

[mcmcd99]

I did move it once, and i just now checked the trusted locations. Functionality is the same, but the reports buttons still arent working.

[davegri]

DO a compact/repair on the DB. It started working for me when I did that.

[mcmcd99]

I did, but it's still not working for me. ?!

[mcmcd99]

For example: When I do my "all by type" button, and do 11/27/2017 it only shows one entry and not all of them as it should.

[mcmcd99]

Oh you know what.. I just noticed that the one result it is showing is the one that has the date (tsdate) as 11/27/2017 (manually entered) and not the timestamped ones that say 11/27/2017 1:16:20 PM as they seem to store the whole chunk of date info in that field.. So that means my button on click macros are not working, because the code: [qry_date]![tsdate] Like IIf(Not [Enter Date: MM/DD/YYYY] Is Null,[Enter Date: MM/DD/YYYY],"*") isnt searching for the date within the date stamp as it should. D:
I don't know what I could do to remedy that.

[davegri]

The db you posted doesn't have an "all by type" button on the frm_Print form.

[mcmcd99]

I mean All by Date, my appologies. It's a very inefficient way of sorting admittedly

[davegri]

OK, I think you need to strip off the time portion of the tsdate field in the table. The date is stored left of the decimal, and time is right of the decimal. So if you truncate tstime to an integer, digits to the right are discarded. You can't really see the actual way the date is stored as a number because the format is set to mm/dd/yyyy (short date), but the underlying data doesn't look anything like that.
Then in the log form and edit log form, you need to code the after_update event for the tsdate field to do the same (make it integer).
I've done all this in the attached db (I've run a one time update query on the table, and added the after_update code to the forms).
If you've submitted a partial log table as a sample, you can run the update query against your full table. You can even run it multiple times as it will only actually change records that have the time portion included in the tsdate field.
mcmcd99-ARFF-v01.zip

[mcmcd99]

Thank you so much. I am just about to leave work now but I will take a look at it first thing Wednesday morning.

[mcmcd99]

Is there a way I can use this method to strip the date from the tstime in my table as well?

[davegri]

mcmcd99-ARFF-v02.zip
You bet. To strip off the date part, you have to get rid of the digits to the left of the decimal. To do that just subtract the Integer part from the whole - that will leave only the decimals to the right.

tstime - int(tstime)

The new db contains a query to update the table (done) and includes the above code in the log form and edit form.
Again you can run the update query against the table any time you feel necessary. It won't do anything to the tstime fields that are already OK.

[mcmcd99]

Great! I am looking at the new one now. Will that not allow me to format them in 24 hour clock anymore?