Not Enough Digits SQL (1 Viewer)

[stu_c]

Hello
I am using the below code, what SQL code would I need to use to show REFstatus to read "NOT ENOUGH DIGITS" or "TOO MANY DIGITS" along with the below

SELECT TBLALLHRDetails.HRRefID,
    TBLALLHRDetails.IDFKFullStaffDetailsID,
    TBLALLHRDetails.HRReferenceNumber,
    TBLALLHRDetails.OCCReferenceNumbers,

Switch(IsNull(TBLALLHRDetails.HRReferenceNumber + TBLALLHRDetails.OCCReferenceNumbers) Or
        TBLALLHRDetails.HRReferenceNumber="N/A" Or
        TBLALLHRDetails.OCCReferenceNumbers = "N/A", Null,
        TBLALLHRDetails.HRReferenceNumber <> TBLALLHRDetails.OCCReferenceNumbers, "Do Not ",
         True, "They ") + "Match" As RefStatus

FROM TBLALLHRDetails;

Many Thanks!

 

[Ranman256]

select field ,getMsgOnLen([field]) from table.

public function getMsgOnLen(pvFld)
select case true
   case IsNull(pvFld)
      getMsgOnLen= "Not Enough"

   case Len(pvFld)< 6
      getMsgOnLen= "Not Enough"

   case Len(pvFld)> 6
      getMsgOnLen= "Too many digits
  
 case else
      getMsgOnLen= "correct"
end select

 

[stu_c]

select field ,getMsgOnLen([field]) from table.

public function getMsgOnLen(pvFld)
select case true
   case IsNull(pvFld)
      getMsgOnLen= "Not Enough"

   case Len(pvFld)< 6
      getMsgOnLen= "Not Enough"

   case Len(pvFld)> 6
      getMsgOnLen= "Too many digits
 
case else
      getMsgOnLen= "correct"
end select

i want it to work with the rest of the code above if possible? also where do I put the ,getMsgOnLen([field]) in the query?

 

[cheekybuddha]

You don't say how long the Reference numbers should be!

Using 6 as the correct length as in Ranman's example you could use SQL like:

SELECT
  HRRefID,
  IDFKFullStaffDetailsID,
  HRReferenceNumber,
  OCCReferenceNumbers,
  Switch(
    IsNull(HRReferenceNumber + OCCReferenceNumbers) OR
      HRReferenceNumber = 'N/A' OR
      OCCReferenceNumbers = 'N/A', Null,
    HRReferenceNumber <> OCCReferenceNumbers, 'Do Not Match',
    (HRReferenceNumber & '' = OCCReferenceNumbers & '') AND Len(HRReferenceNumber) < 6, 'NOT ENOUGH DIGITS',
    (HRReferenceNumber & '' = OCCReferenceNumbers & '') AND Len(HRReferenceNumber) > 6, 'TOO MANY DIGITS',
    True, 'They Match'
  )  AS RefStatus
FROM TBLALLHRDetails;

The following would probably also work:

SELECT
  HRRefID,
  IDFKFullStaffDetailsID,
  HRReferenceNumber,
  OCCReferenceNumbers,
  Switch(
    IsNull(HRReferenceNumber + OCCReferenceNumbers) OR
      HRReferenceNumber = 'N/A' OR
      OCCReferenceNumbers = 'N/A', Null,
    HRReferenceNumber <> OCCReferenceNumbers, 'Do Not Match',
    Len(HRReferenceNumber) < 6, 'NOT ENOUGH DIGITS',
    Len(HRReferenceNumber) > 6, 'TOO MANY DIGITS',
    True, 'They Match'
  ) AS RefStatus
FROM TBLALLHRDetails;

Probably can be shortened further to:

SELECT
  HRRefID,
  IDFKFullStaffDetailsID,
  HRReferenceNumber,
  OCCReferenceNumbers,
  Switch(
    (HRReferenceNumber & '' = OCCReferenceNumbers & '') AND Len(HRReferenceNumber) = 6, 'They Match',
    HRReferenceNumber & '' = 'N/A' OR OCCReferenceNumbers & '' = 'N/A', Null,
    (HRReferenceNumber & '' = OCCReferenceNumbers & '') AND Len(HRReferenceNumber) < 6, 'NOT ENOUGH DIGITS',
    (HRReferenceNumber & '' = OCCReferenceNumbers & '') AND Len(HRReferenceNumber) > 6, 'TOO MANY DIGITS',
    True, 'Do Not Match',
  ) AS RefStatus
FROM TBLALLHRDetails;