Search results

I think it might be best if I try to explain a little better. As i'm not sure the references look right to what I need... I have a main form and that mainform contains two separate subforms. Both subforms are linked to the mainform by an ID. The first subform gets data from SQL Server table...

Hi Pat, thanks for asking. The original data sits on SQL Server, but I need to be able to duplicate very specific information from this database into Access for an entirely separate purpose. With regards to record duplication, yes, certain records will have multiple entries against it i.e...

Hi all. I have a parent form with two sub-forms on it. The first sub-form is filtered by selection on parent form. However what I want to do is double click the record in the first sub-form which will then populate an entry in the second sub-form. If I double click again on the first sub-form I...

As always, excellent responses, thank you very much!

Hi, having sorted my previous issue with nz, I am now stuck trying a similar approach to dates. I have my mainform, and a subform is updated with a button onclick event. My mainform contains two textboxes which are formatted for dates and has datepickers etc. My query runs ok using NZ but as...

Thank you all for your responses, finally got it working. Now I need to have the subform use this instead of the standard query when it loads up :)

I have just tried the code and it worked, however I have another question... Nz([Recnum], " & Me.txtRecNo & ") LIKE '*" & Me.txtRecNo & "*' I'm not sure if I understand this right but it looks like the NULL replacement is whatever is in the textbox on the mainform?

Out of curiosity, why would this work from the WHERE clause and not the initial SELECT part?

Because the example code I posted above has a loop in it that a standard query wont allow for.

I appreciate that, I have tried just using a query in query design and this works perfectly, essentially replacing null values with an empty string, which stops the row from being ignored, but doing this in VBA and passing that as the query through that way is the ideal solution for me here. I...

Thanks for that one, i've tried that but it still didn't include the rows where the value is null. I'm not sure what to try next...

Hi, i'm struggling with using Nz with SQL in VBA, I believe it's the use of " that might be the issue. Would one of you kind fellows be helpful enough to show me where I might be going wrong? The idea is that there is some data in a single table which isn't relational, but the user wants to be...

Hi MajP, thank you for that pointer, yes, I can see why the split & "" would make a string and not a null, that's great! Thank you for your help!

Hi, thank you for the push especially with how to look at the FOR loop. It took me the better part of today just trying to figure a few things out and being honest, I wasn't quite sure what your code above was doing, so after some experimentation, this is what I came up with based on what you...

Thank you for coming back so quickly. My apologies, it seems the join function might not be what i'm looking for. I want to reduce the number of controls on the form from 6 textboxes down to 1, but I still want the same functionality of being able to search for multiple LIKE's within one field...

Hi all, I am trying to gather a bit more knowledge and am working towards a search form which will help me through my day a bit more efficiently. I have a mainform, and a subform. The main form is unbound and has several textboxes which when not null or empty, are used as query criteria to...

I'm afraid theDBGuy your code didn't quite work out for me, but thanks to both you and arnelgp, the second set of code worked beautifully. Very much appreciated and that one little thing changes the dynamic of a very useful tool in the office. Very kind!

Hi, I am running a form with a subform, the main form has several search boxes but I want the query to work primarily from two. i.e. OD and ID What i'm after is typing a number in the OD textbox, same for the ID textbox and the query return any record where the OD is between = OD-1 and OD+1...

Hi, thanks for replying again. Please find the screenshots for my set up and the results from the query on the subform i receive. As you can see, the query does work, it just doesn't work on an individual basis. I am positive that this is because the query demands that both boxes be filled...

Hi both and thank you for taking the time to respond. Just so you are aware, the code I wrote above works perfectly fine in the query as I have already tested it. The problem I have is that I want the list to filter down with the rules I have in there rather than making everything dissappear...