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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Return All Records If No Match In Criteria, Any Version    
 
   
jimbofoxman
post Dec 21 2017, 10:45 AM
Post#1



Posts: 358
Joined: 4-April 08



I have the following query feeding a continuous form. I'm trying to avoid using some sort of login function and just read the computer name instead.

CODE
SELECT tblCustomer.Customer, tblContracts.ContractNo, tblContracts.ContractVerNo, tblContracts.Signed, tblContracts.ConfirmationDate, tblContracts.InvoiceNo, tblEstimators.EstimatorInitials, tblEstimators.ComputerName, tblContracts.ContractID
FROM tblEstimators INNER JOIN (tblCustomer INNER JOIN tblContracts ON tblCustomer.CustomerID = tblContracts.CustomerID) ON tblEstimators.EstimatorID = tblContracts.EstimatorID
WHERE (((tblContracts.Signed)=No) AND ((tblContracts.ConfirmationDate) Is Not Null) AND ((tblContracts.InvoiceNo) Is Null) AND ((tblEstimators.ComputerName)=fOSMachineName()))
ORDER BY tblCustomer.Customer, tblContracts.ContractNo, tblContracts.ContractVerNo;


Their is a global function called fOSMachineName() that I use to capture the name........as follows;
CODE
Function fOSMachineName() As String

'Returns the computername
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
End Function


I have a table that contains all our estimators, that would have their computer names in a field. Using "(tblEstimators.ComputerName)=fOSMachineName()" it works great, but we have a few people that use this program in an admin role. I would like to have the query return every record if the ComputerName is not in the table.

Did some googling (maybe not the right criteria) and didn't come up with much. I saw one suggestion that said to try something like this: Like Nz(fOSMachineName(),"*") but that didn't return all records on a no match.

Any thoughts? Thanks as always.
This post has been edited by jimbofoxman: Dec 21 2017, 10:46 AM
Go to the top of the page
 
theDBguy
post Dec 21 2017, 11:04 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Probably not the "best" way, but for just a quick solution, could you please try something like:

tblEstimators.ComputerName=fOSMachineName OR DCount("*","tblEstimators","ComputerName='" & fOSMachineName() & "'")=0

(untested)
Hope it helps...
Go to the top of the page
 
jimbofoxman
post Dec 21 2017, 11:14 AM
Post#3



Posts: 358
Joined: 4-April 08



Yes, that does work........takes a second but it did work. I did try a DCount function.........but not like you did and I had some syntax error or something.
Go to the top of the page
 
nuclear_nick
post Dec 21 2017, 11:17 AM
Post#4



Posts: 1,619
Joined: 5-February 06
From: Ohio, USA


It could also be the implementation of the 'Like' scheme.
Original...
CODE
tblEstimators.ComputerName=fOSMachineName()

Try...
CODE
tblEstimators.ComputerName Like "*" & fOSMachineName() & "*"


I've also turned it around in the QDG, so instead of Field being Field, Table being table, Criteria being Criteria, I've the Field being the Criteria, the Table being blank, and Criteria being the Table.Field. So that makes the 'WHERE' look like this...
CODE
fOSMachineName()=tblEstimators.ComputerName

... and then you add another OR...
CODE
fOSMachineName()=tblEstimators.ComputerName OR fOSMachineName()=""


If the 'machine name' is blank, it should return them all, else match the machine.
Go to the top of the page
 
theDBguy
post Dec 21 2017, 11:24 AM
Post#5


Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi,

DCount() is not always the best solution but it might have to do until you find a better one.

Cheers!
Go to the top of the page
 
tina t
post Dec 21 2017, 01:52 PM
Post#6



Posts: 5,560
Joined: 11-November 10
From: SoCal, USA


the only way i can think of, to avoid a domain function in the query, would be to put an unbound, hidden textbox control on the form (header or footer section), putting a DLookup() in the ControlSource property, as

=DLookup("ComputerName","tblEstimators","ComputerName='" & fOSMachineName() & "'")

then change the query's WHERE clause a little bit, to

WHERE tblEstimators.ComputerName=fOSMachineName OR Forms!MyFormName!MyTextboxName Is Null

you might have a timing issue with this solution, with the query (as the form's Recordsource) running before the DLookup() function in the textbox's ControlSource. to overcome that, you'd have to load the form and then assign, or requery, the form's Recordsource. hmm, not great, but just another option.

hth
tina
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 09:21 AM