If statement to alter text box value

[eskybel]

Hello,



I have a report with a select query for the source. Each text box on the report references certain fields in the select query. I have a situation where one field, called PO, pulls in PO numbers. The problem is this:

We have a PO in our system as: 123456-001
Our customer has the PO in their system as: 123456

We need the report to show their number, which is ours, minus the -001.

The kicker:
Our POs are not a fixed length, and do not always end in a fixed number or length. Some are 5 digits, or 6, and end in -001, or -01, or -1, or -002, etc, removing the ability to use the left(), right() functions. Is there a way to take our PO number, and tell it to remove and display our PO without anything after and including the dash?

[rzw0wr]

Take a look at the Split(() function.

http://office.microsoft.com/en-us/ac...001228911.aspx
Also
http://www.pcreview.co.uk/forums/spl...-t1172256.html

Dale

[eskybel]

Doesn't seem like those examples can be used in an expression or query. I'm not sure how to adapt this from the second link:

Public Function SplitTest(Test As String, ItemNo As Integer)
On Error Resume Next
' Split into 8 parts. Each part divided by a "-"
SplitTest = Split(Test, "-", 8)(ItemNo)
End Function

to either be attached to a button with code running this function, opening the query, adjusting the PO field, then opening the report and displaying as expected. Still pretty new to this stuff. Thanks!

[rzw0wr]

I threw this together today.
Works from a command button and should work from the Active form event.

Dim rst As DAO.Recordset
Dim A As Integer
Dim Avar As Variant
Set rst = CurrentDb.OpenRecordset("select * FROM table4")
Do While Not rst.EOF And Not IsNull(rst!Testtext)
    Avar = Split(rst("Testtext"), "-")
    With rst
        .Edit
        !Testtext = Avar(A)
        .Update
        .Bookmark = .LastModified
    End With

    'Debug.Print Avar(A), I
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

See how it works.
Needs error handling.

Dale

[eskybel]

Ok so here's what I have in my command button...

Private Sub Command44_Click()

Dim rst As DAO.Recordset
Dim A As Integer
Dim Avar As Variant
Set rst = CurrentDb.OpenRecordset("qryShippingLabel")
Do While Not rst.EOF And Not IsNull(rst)
Avar = Split(rst([PURCHASEORDER]), "-")
With rst
.Edit
![PURCHASEORDER] = Avar(A)
.Update
.Bookmark = .LastModified
End With

'Debug.Print Avar(A), I
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

DoCmd.OpenReport "rptShipLabelPrint"

End Sub

An error "too few parameters. Expected 1" and the debug menu highlights the "set rst =" line.

[rzw0wr]

Which set rst line. first or last?
First line check that the query has data.

Post SQL of query.

This will work for a table also.
Will NOT change if the ffield doesn't have an - in it.



Dale

[eskybel]

Which set rst line. first or last?
First line check that the query has data.

Post SQL of query.

This will work for a table also.
Will NOT change if the ffield doesn't have an - in it.

Dale

Sorry, the first set rst.
Here's the SQL for the query.

SELECT dbo_CUSTPACKINGSLIPJOUR.SALESID, dbo_CUSTPACKINGSLIPTRANS.ITEMID, dbo_CUSTPACKINGSLIPTRANS.EXTERNALITEMID, dbo_CUSTPACKINGSLIPTRANS.NAME, dbo_CUSTPACKINGSLIPTRANS.ORDERED, dbo_CUSTPACKINGSLIPTRANS.QTY, dbo_CUSTPACKINGSLIPTRANS.SALESID, dbo_CUSTPACKINGSLIPTRANS.PACKINGSLIPID, dbo_CUSTPACKINGSLIPJOUR.PURCHASEORDER, dbo_CUSTPACKINGSLIPJOUR.DELIVERYNAME, dbo_CUSTPACKINGSLIPJOUR.DELIVERYADDRESS, dbo_CUSTPACKINGSLIPJOUR.QTY, dbo_CUSTPACKINGSLIPJOUR.DLVCOUNTRY
FROM dbo_CUSTPACKINGSLIPTRANS INNER JOIN dbo_CUSTPACKINGSLIPJOUR ON (dbo_CUSTPACKINGSLIPTRANS.DATAAREAID = dbo_CUSTPACKINGSLIPJOUR.DATAAREAID) AND (dbo_CUSTPACKINGSLIPTRANS.PACKINGSLIPID = dbo_CUSTPACKINGSLIPJOUR.PACKINGSLIPID)
WHERE (((dbo_CUSTPACKINGSLIPTRANS.ITEMID)<>"P000" And (dbo_CUSTPACKINGSLIPTRANS.ITEMID) Not Like "misc*") AND ((dbo_CUSTPACKINGSLIPTRANS.PACKINGSLIPID) Like " " & [Packing Slip #]));

[eskybel]

The query is very simple. I just need it to pull the PURCHASEORDER field, cut off the - and anything after it, and display on the report.

[rzw0wr]

All you need in your query to make my code work is Purchaseorder. Make a new simple form, put a command button on it with the code behind the button.
The code will do the rest.

Dale

[eskybel]

All you need in your query to make my code work is Purchaseorder. Make a new simple form, put a command button on it with the code behind the button.
The code will do the rest.

Dale

I think I misunderstood something along the way. When using the select query straight out, it had problems. I changed the query to a "make table" query, pointed the report at the table, ran the query, ran the command button, and now appears to be doing it right. If there was a way to do it just off the select query, that keeps things simpler?...but this works too! I just had to add a couple things in the code, and we're working.

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim A As Integer
Dim Avar As Variant

DoCmd.OpenQuery "qryShippingLabelTableClear"
DoCmd.OpenQuery "qryShippingLabelTable"
Set rst = CurrentDb.OpenRecordset("select * from tblShippingLabelTable")
Do While Not rst.EOF And Not IsNull(rst!PURCHASEORDER)
Avar = Split(rst("PURCHASEORDER"), "-")
With rst
.Edit
!PURCHASEORDER = Avar(A)
.Update
.Bookmark = .LastModified
End With

'Debug.Print Avar(A), I
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

DoCmd.OpenReport "rptShipLabelPrint", acViewPreview
End Sub

Thanks!

[rzw0wr]

Glad to hear it is working.
Dale

[eskybel]

Ok, another question on this:
What if I have a number, such as 1234-5678-910. Is there a way to eliminate just the dashes, leaving the rest of the value intact. It would read out: 12345678910

I tried adding a -1 to the split arguments, but it's still only returning the first substring, and missing the numbers after the first dash.

Thanks,
Adam

[eskybel]

And possibly...is there a way to make this code strip off any letters, and leave only numbers? Ex: AB123-4567CDE would become 123-4567 (or 1234567)?

[June7]

Replace(x, "-", "")

Stripping all alpha characters requires VBA custom function. http://support.microsoft.com/kb/210537