DCount dilemma (1 Viewer)

[KateM]

Hi Everyone

Apologies for what might well be yet another novice question !

I have a form with a subform from which the user selects a record, which then populates the form.

The problem is that some (but not all) of the historical name, salutation and address fields in the original source database have stars in the fields after the name, salutation, etc., to denote that there was a comment regarding the record, etc. Some have no stars at all, others might have them against name and address 1, others against more of the address.

I am not permitted to remove these stars from the original source database, however we don't want these stars to be continued in the table which my form subsequently fills...

As an example, the Name will be in the database dbo.measurement, in a column called Name.

The name could be Mr J Smith***************

Meas_id and NumOrigId are the same reference number, just one is in the source database, the other on my form.

So far I've tried using DCount but I get an error message telling me that there are the wrong number of arguments or invalid property assignment. For example:

If DCount("*", "dbo_Measurement", "Name", "[Meas_id] = Forms!FrmRetestSearch.NumOrigId") > 0 Then
Me.TxtName = Replace(Me.TxtName, "*", "")
End If


If I just use Me.TxtName = Replace(Me.TxtName, "*", "") without any precursor if statement then when I test a record without any stars after the "name" then I get an error message, so I must have to use an if statement...

It doesn't help that stars are wildcards in VBA..


Please advise - how do I structure an if statement so that it finds where there is one or more stars in the name column of dbo_measurement against the meas_id / NumOrigId stated?

Thank you !

Kate

 

[pr2-eugin]

Honestly, I did not read the entire post. Just saw the code. Try the following suggestion.

If DCount("*", "dbo_Measurement", "Name", "[Meas_id] = [COLOR=Red][B]" &[/B][/COLOR] Forms!FrmRetestSearch.NumOrigId) > 0 Then
     Me.TxtName = Replace(Me.TxtName, "*", "")
End If

I take Meas_ID is a Number field. If it is Text, try.

If DCount("*", "dbo_Measurement", "Name", "[Meas_id] =[COLOR=Red][B] '" &[/B][/COLOR] Forms!FrmRetestSearch.NumOrigId [COLOR=Red][B]& "'[/B][/COLOR]") > 0 Then
     Me.TxtName = Replace(Me.TxtName, "*", "")
End If

 

[KateM]

Hi Paul

Thanks for your reply, but I'm still getting an error.

This time it is :

"Compile error:

Wrong number of arguments or invalid property assignment"


It must be the fact that I've got the source database and the source column listed - I'm trying to reuse a previous DCount statement which didn't have the column name in it by adapting it...

 

[pr2-eugin]

Right saw the error. Cannot believe I look past it

Any domain function takes in (required) 2 arguments and one optional argument, yours has 4. That is the problem. I have a post about the Anatomy of Domain function, read that for more info.

 

[namliam]

Replace("Mailman ***", "*", "")
Replace("The Mailman", "*", "")
Replace("*** Mailman", "*", "")
Replace("Idiot ** Paul", "*", "")
Replace("Magical Paul", "*", "")

That should all work
Only reason replace should fail is if the replace is tried to do on a NULL value.... A simple:
NZ(YourColumn)
or
if not Isnull(Yourcolumn) then replace....

Should help prevent that

 

[pr2-eugin]

Should help prevent that

Do not think that is the problem here ! (atleast for now)

Check my comment in Post#4. I am so glad I am not the only person who looked past that. Ha Ha ! Come on now; hide your tail between your legs !

 

[KateM]

Hi The Mailman

I can't use if not isnull because the Name, Salutation or Address fields will have text and numbers in them, it is just the stars that I want to remove.

Equally because we've more than 6 million distinct records, I can't pre-empt what the name, salutation or address is going to be.

The code has to work for all circumstances, wherever there are stars in the fields after the text or number and text. (I might be misunderstanding your example - if that is the case, I apologise.)

Hi Paul

Thanks for the explanation.
Is there a way to specify the dbo_measurement database and the name column within that database as a single entity ?

I tried "dbo_measurement.name" but that didn't work

Should I try SQL in this instance ?

 

[pr2-eugin]

Hi Paul

Thanks for the explanation.
Is there a way to specify the dbo_measurement database and the name column within that database as a single entity ?

I tried "dbo_measurement.name" but that didn't work

Should I try SQL in this instance ?

I read your post again, you do not need DCount here. DCount only counts the number of records in the table/query. What you want to do is check if starts are in the Name for a corresponding number is this correct? Then you might need a DLookup instead, and then a InStr function.

If [URL="http://www.techonthenet.com/access/functions/string/instr.php"]InStr[/URL]([URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](DLookup("[nameFieldName]", "yourTableName", "yourIDField = " & Forms!yourFormName!yourIDControlName), "NothinToSee"), "*") <> 0 Then
    MsgBox "There are some stars in the String !, vbInformation
End If

 

[namliam]

\I can't use if not isnull because the Name, Salutation or Address fields will have text and numbers in them, it is just the stars that I want to remove.

WHY cant you use the Isnull??? seriously did you even consider it or try it?

:banghead:

The ONLY reason why replace WILL fail or cause any errors if there is a field containing a NULL .... otherwize empty field or any "real" or "normal" value with or without * in there.

:banghead:

 

[KateM]

Hi Paul

I agree with you, I don't need DCount - I do need to count the number of stars in a field not the number of records with stars...

I have successfully adapted your suggested code as follows:

If InStr(Nz(DLookup("[Name]", "dbo_measurement", "meas_id = " & Forms!FrmRetestSearch.NumOrigId), ""), "*") > 0 Then
Me.TxtName = Replace(Me.TxtName, "*", "")
End If

Thanks again for all your help !

 

[pr2-eugin]

Most welcome ! Good Luck !

 

[namliam]

You dont need the DLookup either... or even check for "*" at all

 

[pr2-eugin]

You dont need the DLookup either... or even check for "*" at all

Reading this man's reply. I doubt the same ! What is your current form setup again? I think you are a bit confused.

Is this Form bound to the Table? If so you do not need a DLookup, just a replace would do. Or you are trying something different? Explain a bit.

 

[KateM]

The form is unbound.

The form updates a table which is then used by other staff members for issuing packs and which should not have any stars in the fields.

I am not allowed to amend the original table, so have to use DLookup to populate the form, then the instring query to remove the stars, before the table can be populated.

The star removal code works perfectly !