Reformat a Field to match external data

[mattstrachan]

I am attempting to use some external data to populate fields in my DB. I would like to reformat the ProductID in my DB to match a ProductName coming into my DB.

We have many products that have 2 pieces. If the product does have two pieces, the external database has two ProductNames that look like this:

0000967
2000967

I would like my database to be able to pull information for each of these 2 part products (they will be displayed as one product in our DB, never to be seperated). I have a form that gives the exact measurements of the first piece by using the ProductName and matching my ProductID (0000967). I would now like to write a little VBA to populate some fields on the forms that are pulled from 2000967.

something like:
Forms!Product!txtField2 = DLookup("[FieldName]", "TableName", "[ProductName] = Forms!Product!ProductID")

The issue is that I need to only get the trailing 6 digits of my ProductID and add a 2 at the beginning. Is there a quick Format syntax I could use to accomplish this?

 

[billmeye]

You could try:

Forms!Product!txtField2 = "YourFirst2Digits" &  Right(DLookup("[FieldName]", "TableName", "[ProductName] ='" & Me.ProductID & "'"),6)

 

[mattstrachan]

Thank you, this code almost worked. So using your idea gave me the result of:

2967

I need it to return:

2000967

So I am assuming that the Right( ,6) when used with Number removes the leading zeros?

Would there be a way to format it something like Right( , 000000) and tell it how many digits there should be?

 

[billmeye]

Using the code I provided says to select the right 6 characters of the string field. But, if Access thinks it is a number, it will truncate the leading zero's so I'm guess that is what is happening. Try this:

Right([COLOR="Red"]CStr([/COLOR]DLookup("[FieldName]", "TableName", "[ProductName] ='" & Me.ProductID & "'")[COLOR="red"])[/COLOR],6)

This will convert what is found in the Dlookup to a string and then select the right 6 characters.

 

[mattstrachan]

MsgBox (2 & Right(CStr(Forms!Product!ProductID), 6))

Still getting:

2967 instead of 2000967

I thought I would break the part I want to format away from everything else for the time being. Once I can make the code above result the correct format i will dump it back into my DLookUp.

Any more advice? Is my syntax incorrect? I have checked it several times and it looks correct to me.

 

[billmeye]

Is ProductID data type an Integer or Text or do you have formatting set to a number? Just playing around I'm not having any problem getting Right(ProductID,6) to yield 000967. If you can, attach your DB and I will look at it.

 

[mattstrachan]

The ProductID field is an AutoNumber set to Long Int, Increment, and the Format is "0000000". I will attach a copy as soon as I can if it will help.

 

[mattstrachan]

Is there a reason the a 7 digit Autonumber field would not be able to be converted to a String? The reason I ask is because if I put in this code:

CStr(Me.ProductID)

My MsgBox still shows 967 instead of 0000967.

Does this mean that the ProductID is actually being passed in as just 967?

The table it is stored in shows 0000967 but I am just wondering why I can not get this to work.

 

[billmeye]

That is why it is always coming up wrong, any number field will truncate leading zero's, the formatting only affects viewing the field info but not what is stored in the field.

So, I recommend adding some code that will fill back in the missing 0's based on the current value. I'm guessing 6 digits is the longest this number will ever be, so:

Forms!Product!txtField2 = "YourFirst2Digits" 
If Forms!Product!ProductID < 10 '1 digit Then
    Forms!Product!txtField2 = Forms!Product!txtField2 & "00000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 100 Then Forms!Product!txtField2 = Forms!Product!txtField2 & "0000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 1000 Then Forms!Product!txtField2 = Forms!Product!txtField2 & "000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 10000 Then Forms!Product!txtField2 = Forms!Product!txtField2 & "00" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 100000 Then Forms!Product!txtField2 = Forms!Product!txtField2 & "0" & Forms!Product!ProductID
Else
    Forms!Product!txtField2 = Forms!Product!txtField2  & Forms!Product!ProductID
End If

 

[mattstrachan]

Well that worked for me. I am now getting the correct ProductID to pass through. Thank you for all of your help today!

 

[billmeye]

Your welcome. Sometimes brute force is the only way I have found to solve some simple sounding problems.

 

[mattstrachan]

Ok, So now I have my ProductID conversion taken care of. Now I would like to populate the form for 5 fields. Length, Width, Height, Weight, and another field that is called TablePercent. I have the form working for the first four fields. The last field is a calculated field that is pulling more data from an external source.

I have created a Query that populates this for the first item in my Products Form. The second item is what we worked on getting the ProductID for yesterday. It is the same number but with a leading 2 instead of 0. The query looks like this:

SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent
FROM FingerPrint, Stones
WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)=Forms!Product!ProductID));

Because I had to reformat the ProductID I am not sure how to get this field populated with the new ProductID that was created in VBA using your code from above. I have tried creating a new query but since the ProductID was created in VBA I am not sure how to call it to the Query but also have it relate to the Forms!Product!ProductID

Any help on this one?

I am trying my best to explain this but if anyone would need more information let me know.

 

[billmeye]

I'm not quite sure I'm following you, but in VBA you can create and run queries so you have more control over variables.

Docmd.RunSQL "SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent FROM FingerPrint, Stones WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)=Forms!Product!ProductID));"

What I'm not following is where you need to reformat. Is it the StoneName that needs to to formatted as before?

Dim StName as String

StName = "YourFirst2Digits" 
If Forms!Product!ProductID < 10 '1 digit Then
    StName = StName & "00000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 100 Then StName = StName & "0000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 1000 Then StName = StName & "000" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 10000 Then StName = StName & "00" & Forms!Product!ProductID
    ElseIf Forms!Product!ProductID < 100000 ThenStName = StName & "0" & Forms!Product!ProductID
Else
    StName = StName  & Forms!Product!ProductID
End If

Docmd.RunSQL "SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent FROM FingerPrint, Stones WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)='" & StName & "'));"

Correct me if I misunderstood you.

 

[mattstrachan]

I think you are following me. So could I do something like this:

txtTablePercent = Docmd.RunSQL( )

I am trying to make that text field equal the Select Statement that you typed above. I am not familiar with running SQL inside of VBA. Would that be a correct method of making a field equal a SQL Statement?

 

[billmeye]

My bad. I wasn't thinking. Your trying to return the value to your form. The only way I know of is to use your query as the recordsource for a recordset:

Dim MySql as String, Rst as Dao.Recordset

MySql = "SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent FROM FingerPrint, Stones WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)='" & StName & "'));"

Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapShot)
If Rst.Recordcount > 0 Then
Rst.MoveFirst
Me.txtTablePercent = Rst!Fields!TablePercent
Rst.Close
Set Rst = Nothing
Else
Msgbox "No value returned"
End If

 

[mattstrachan]

I am getting a Data type mismatch in criteria for:

Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)

Could this be coming from MySql? The error occurs after my select statement but I know that StoneName is Number and ProductIDStone2 is String. But this next line of code works just fine:

Forms!Product!txtStoneWeight2 = DLookup("[StoneWeight]", "Stones", "[StoneName] = " & ProductIDStone2)


ProductIDStone2 is the name of the variable that is StName in your code.

 

[mattstrachan]

So I have read a few articles that describe this error and why it occurs. The articles say that ADO has a higher priority that DAO. It says to make sure that the DAO has a higher priority in the REFERENCES dialog box. I have looked but I do not know where this is. Could this be a solution?

 

[billmeye]

The error occurs after my select statement but I know that StoneName is Number and ProductIDStone2 is String. But this next line of code works just fine:

Forms!Product!txtStoneWeight2 = DLookup("[StoneWeight]", "Stones", "[StoneName] = " & ProductIDStone2)

That is interesting since you are saying ProductIDStone2 is a text field but your Dlookup works without single quotes suggesting ProductIDStone2 is actually a number field. Your Dlookup would not work without single quotes if it were a text field.

Now, if StoneName is a number field, then this would be incorrect syntax:

And ((Stones.StoneName)='" & StName & "'

it should be:

And ((Stones.StoneName)=" & CInt(StName) & "

With that being said, I'm a little confused about the fields in your table and what they are storing, Sorry.

 

[mattstrachan]

I can post my db here if that may help show what is going on. There are links to external databases though that I am not sure will show up and most of the data is being pulled from them.

Here is my full code for the problem I am working:

Private Sub Form_Current()
Dim ProductIDStone2 As String

‘ This will generate the ProductID number for the second stone in a matched pair

Select Case Forms!Product!Qty_Type
Case "Matched Pair"
If Forms!Product!ProductID < 10 Then ' 1 digit
ProductIDStone2 = 200000 & Me.ProductID
ElseIf Forms!Product!ProductID < 100 Then ' 2 digit
ProductIDStone2 = 20000 & Me.ProductID
ElseIf Forms!Product!ProductID < 1000 Then ' 3 digit
ProductIDStone2 = 2000 & Me.ProductID
ElseIf Forms!Product!ProductID < 10000 Then ' 4 digit
ProductIDStone2 = 200 & Me.ProductID
ElseIf Forms!Product!ProductID < 100000 Then ' 5 digit
ProductIDStone2 = 20 & Me.ProductID
ElseIf Forms!Product!ProductID < 1000000 Then ' 6 digit
ProductIDStone2 = 2 & Me.ProductID
End If

Forms!Product!txtStoneLength2 = DLookup("[StoneLength]", "Stones", "[StoneName] = " & ProductIDStone2)
Forms!Product!txtStoneWidth2 = DLookup("[StoneWidth]", "Stones", "[StoneName] = " & ProductIDStone2)
Forms!Product!txtStoneHeight2 = DLookup("[StoneHeight]", "Stones", "[StoneName] = " & ProductIDStone2)
Forms!Product!txtStoneWeight2 = DLookup("[StoneWeight]", "Stones", "[StoneName] = " & ProductIDStone2)

' This next code is to populate the Table percent for the second stone in a matched pair

Dim MySql As String, Rst As Dao.Recordset

MySql = "SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent FROM FingerPrint, Stones WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)='" & ProductIDStone2 & "'));"

Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveFirst
Me.txtTablePercent2 = Rst!Fields!TablePercent
Rst.Close
Set Rst = Nothing
Else
MsgBox "No value returned"
End If



As you can see, ProductIDStone2 is declared as a String. The DLookUp code works just fine though. The error is for a datatype mismatch and the error occurs at:

Set Rst = CurrentDb.OpenRecordset ()

 

[billmeye]

The question is regards to the data types and without the actual tables can't tell. Try using this code:

MySql = "SELECT (FingerPrint.TableDiamtr/FingerPrint.AvragDiamtr) AS TablePercent FROM FingerPrint, Stones WHERE (((FingerPrint.StoneID)=Stones.StoneID) And ((Stones.StoneName)=" & CLng(ProductIDStone2) & "));"