UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Problems Building Search Form, Access 2016    
 
   
namron
post Mar 6 2018, 08:29 AM
Post#21



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


Hi Guys

Unfortunately I still have a problem. Although the search works great now - it is still not including any new records that I add to the table? Seems very odd.

I'm attaching a copy of the sample database - I confirm that all data in it is fictitious.

Attached File  ALM_Database_Sample_06_03_18.zip ( 69.05K )Number of downloads: 7


Can anybody see why new record do not show up in the search!!

Thanks

Norman

Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 09:20 AM
Post#22


UA Admin
Posts: 33,514
Joined: 20-June 02
From: Newcastle, WA


Please define "new records". YOU know which records you consider "new", but it's not all that easy to tell the difference otherwise.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
namron
post Mar 6 2018, 10:06 AM
Post#23



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


Hi

When I enter a new record into the table it isn't found when I do a search using the search form. I can find those new records using the built in search.

In my sample database the last 2 records, Gareth Hankinson and George Best, are not found when using the search form. Those records were added after I'd finalised the search form and the query that it opens.

Thanks
Go to the top of the page
 
zaxbat
post Mar 6 2018, 10:54 AM
Post#24



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Your user interface seems very limited and labor intensive to both the programmer and the end user. Look at this and see if it gives you any ideas. Oh, and maybe you should not put real client data into your DBs that you share here in UA. Just scramble the names or something so they are not identifiable maybe.
Attached File(s)
Attached File  BrowseClientsDemo.zip ( 293.75K )Number of downloads: 11
 

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
namron
post Mar 6 2018, 11:57 AM
Post#25



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


Thanks.

The sample database I provided was 'stripped down' and was purely for the purposes of trying to get assistance with the operation of my search form.

As I've mentioned previously the data provided was all fictitious.
Go to the top of the page
 
zaxbat
post Mar 6 2018, 12:01 PM
Post#26



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Ah, cool....sorry then.

Did you look at the zip file I attached?
Access automatically allows all of the searching that you were manually building and a lot more. Sort, Filter, Search already there and not limited to small window....can see everything and make sure you have the correct record very easily. Adding, deleting, and editing is all there already. So you do not have to do all of that work.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
namron
post Mar 6 2018, 12:54 PM
Post#27



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


“So you do not have to do all of that work”
... unless of course I was trying to build a bespoke interface requested by the organisation I am building the database for.
Go to the top of the page
 
RJD
post Mar 6 2018, 02:31 PM
Post#28


UtterAccess VIP
Posts: 8,806
Joined: 25-October 10
From: Gulf South USA


Hi: There were two issues that I could find that would affect the query results...

1. Some of the fields contain Nulls, and you cannot make a Null equal a Null. Doesn't work. So I redid the query to convert the Nulls to ZLS for comparison. That seems to clear up the missing records issue.

2. Apparently you are using European dates, given how you named the attached file, and how you are asking for the data entry. I can't fully test that here (using US dates) but used one of the methods to convert the dates to comparable formats. Access likes to compare based on US format assumptions.

Test this and see if it works in your date environment, and let us know if we need to look at this further. Perhaps someone in a location using European dates can test and correct if necessary.

HTH
Joe
Attached File(s)
Attached File  ALM_Database_Sample_6_3_18_Rev1.zip ( 48K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
namron
post Mar 6 2018, 04:27 PM
Post#29



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


Hi Joe

Thanks so much for your input on this. I must admit I don't follow the technicalities of your solution or the date format issue.

That works fine now, including dates .. but I was curious about the different syntax required in the query for the two fields containing Nulls and it crossed my mind that somebody may also omit a first or other name. I tested this by deleting some first or other names and then searching for those records. Sure enough the records did not come up in a search. I tried adapting the two name criteria following the logic you applied to the other fields but it didn't work.

I suppose I could make the name fields 'required'? .. or would you have time to amend the query for me to allow for Nulls in the name fields?. I have actually come across individuals who are clients of this organisation who claim they only have one name, i.e. no other names.

Thanks again

Norman

Go to the top of the page
 
RJD
post Mar 6 2018, 07:12 PM
Post#30


UtterAccess VIP
Posts: 8,806
Joined: 25-October 10
From: Gulf South USA


Hi again: The two name fields have been modified to accept Null values from the table. See attached.

The reason for the revised syntax in the case of Nulls in the table field: You cannot equate Nulls with Nulls. So if the form control is Null and the Field value in the table are both Null - they are NOT equal to each other - so you will not get that record. Nulls are not equal to anything, so they have to be converted to something else. In this case I used the NZ function to convert them to a zero-length string (which is NOT a Null, and can be equal to itself).

The Null issue was raised because of the way I did the criteria - avoiding the virtually undecipherable rewrite Access does with the criteria when you do all those OR IsNull criteria.

And the last two records you had in the table are different in that earlier records with that "empty" field most likely contain ZLSs already, so they displayed. The last records seem to be definitely Null in those fields. One thing I do sometimes is set a default value of "NA" in some fields just to let me know that no actual value was entered. Just a thought ...

In the case of the dates, Access likes to use US format dates (mm/dd/yyyy) for comparisons. When you use European dates, the comparisons are still made to a US format date. In actual fact, the underlying date is not a date at all, but a sequence number from the first date recognized by Access (I always have to look that one up, Dec 30, 1899).

That sequence number is converted to a date you recognize when you need to display it. There are at east a couple ways to handle this comparison, and I just showed you one of them. I'll let our European date format folks give you any other approaches if you need/want them.

I am lounging away at the beach right now, so may not be completely sharp explaining all this! But is does work, even if there might be an easier/simpler solution to be found.

HTH
Joe


Attached File(s)
Attached File  ALM_Database_Sample_6_3_18_Rev2.zip ( 47.42K )Number of downloads: 14
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
namron
post Mar 7 2018, 06:37 AM
Post#31



Posts: 84
Joined: 31-August 11
From: Lancashire,UK


Hi Joe

Thanks again for the updated version and also for the detailed explanation. It always amazes me how people on this forum are so patient and generous with their time.

Enjoy your break.

Norman

Go to the top of the page
 
GroverParkGeorge
post Mar 7 2018, 08:29 AM
Post#32


UA Admin
Posts: 33,514
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

Null is a concept used in databases, including Access, to handle those cases where a value is not known. It is not "blank", it is not "nothing". It means that there may or may not be a value for that particular data point, but we do not know a) if there is a value or not, or b) if there is a value, we don't know what it is. Let's say, for example, we're talking about a person's birthdate. Everyone has to have a birthdate, obviously. But when entering information about that person into a table, you may not know what that date is. It is "Null" in this sense.

There is a legitimate argument about whether we should allow Nulls in our tables. We have the ability to control that, by the way.

Attached File  AllowNulls.jpg ( 96.47K )Number of downloads: 1


The point here is that, as Joe pointed out, Null cannot be compared to anything else logically because it literally means, "No Known". It makes no sense to ask "Is not known the same as July 22, 1999?" And, therefore, all Relational Database Management Systems, including Access, have a mechanism to convert Nulls to a known value, such as 0 or perhaps a default date or an empty string --""-- to enable such comparisons.

HTH

George

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
RJD
post Mar 7 2018, 09:42 AM
Post#33


UtterAccess VIP
Posts: 8,806
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Norman. We are always happy to assist.

And thanks George for jumping in. As I said, I am in another location on the beach, and my concentration might be a bit divided right now!

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 02:06 AM