Find and deleting a # sign in a recordset

M

moosecck

I have a table tbl_Credit_Application_Input that uses text values for
Corporate Name and a DBA/Trade Name columns. Users have a tendency to
input a name like Subway #34545 in one of the columns. When I use my
search query to try to pull the row that includes the Subway #34545 as
part of the criteria, the search comes up blank because of the #
sign. I am looking for a way to identify any record that has the #
sign in it and clean out the # sign so the record can be pulled by the
query. I pull the initial data from tbl_Credit_Application_Input
using an append query into tbl_Info, then use tbl_Info as a temp table
to pull the data into tbl_ID for the final edit screen to view. The
tbl_Credit_Application_Input to tbl_Info query works fine, but getting
the data from tbl_Info to tbl_ID is an issue when the # sign is
involved. The code for the tbl_Info to tbl_ID append query is below:

INSERT INTO tbl_ID ( [Date Rcvd], [Date Completed], Status, Outlet,
[Existing Outlet], [Type of Request], [Corporate Name], [DBA/Trade
Name], [A/P Contact Name], [A/P Phone], [A/P Fax], [Billing Address],
[Billing City], [Billing State], [Billing Zip], [Ship To Address],
[Ship To City], [Ship To State], [Ship To Zip], [Bank Name],
Attention, [Bank Phone], [Bank Fax], [Bank Acct], [T&S Signor],
[Personal Guaranty], [Corporate Record Status], [Date of
Incorporation], [Business Unit], [Reason Declined], [Additional
Decline Reason], [Sales Center], Terms, [Credit Limit], [SAP NOTES/
HEAD OFFICE INFORMATION], [CRI Comments], Selection, Reprocessed,
[Incomplete Reasons], [Cycle Time], [Date Faxed to Bank], [Date Letter
Sent], Defect1, Defect2, Defect3, Defect4, Defect5, Seniors, [Senior
Phone], [Senior Comments], RecordCtrLocNumb, Created_By, Created_Date,
Edited_By, Edited_Date, Worked_By, Change_Worked_By, User_Status_Chg,
Date_Status_Chg, Printed, Printed_Date, Identifier,
Bank_Ref_First_Request, [Profit Center], Profit_Center_Name )
SELECT tbl_Info.[Date Rcvd], tbl_Info.[Date Completed],
tbl_Info.Status, tbl_Info.Outlet, tbl_Info.[Existing Outlet],
tbl_Info.
[Type of Request], tbl_Info.[Corporate Name], tbl_Info.[DBA/Trade
Name], tbl_Info.[A/P Contact Name], tbl_Info.[A/P Phone], tbl_Info.[A/
P Fax], tbl_Info.[Billing Address], tbl_Info.[Billing City], tbl_Info.
[Billing State], tbl_Info.[Billing Zip], tbl_Info.[Ship To Address],
tbl_Info.[Ship To City], tbl_Info.[Ship To State], tbl_Info.[Ship To
Zip], tbl_Info.[Bank Name], tbl_Info.Attention, tbl_Info.[Bank Phone],
tbl_Info.[Bank Fax], tbl_Info.[Bank Acct], tbl_Info.[T&S Signor],
tbl_Info.[Personal Guaranty], tbl_Info.[Corporate Record Status],
tbl_Info.[Date of Incorporation], tbl_Info.[Business Unit], tbl_Info.
[Reason Declined], tbl_Info.[Additional Decline Reason], tbl_Info.
[Sales Center], tbl_Info.Terms, tbl_Info.[Credit Limit], tbl_Info.[SAP
NOTES/HEAD OFFICE INFORMATION], tbl_Info.[CRI Comments],
tbl_Info.Selection, tbl_Info.Reprocessed, tbl_Info.[Incomplete
Reasons], tbl_Info.[Cycle Time], tbl_Info.[Date Faxed to Bank],
tbl_Info.[Date Letter Sent], tbl_Info.Defect1, tbl_Info.Defect2,
tbl_Info.Defect3, tbl_Info.Defect4, tbl_Info.Defect5,
tbl_Info.Seniors, tbl_Info.[Senior Phone], tbl_Info.[Senior Comments],
tbl_Info.RecordCtrLocNumb, tbl_Info.Created_By, tbl_Info.Created_Date,
tbl_Info.Edited_By, tbl_Info.Edited_Date, tbl_Info.Worked_By,
tbl_Info.Change_Worked_By, tbl_Info.User_Status_Chg,
tbl_Info.Date_Status_Chg, tbl_Info.Printed, tbl_Info.Printed_Date,
tbl_Info.Identifier, tbl_Info.Bank_Ref_First_Request, tbl_Info.[Profit
Center], tbl_Info.Profit_Center_Name
FROM tbl_Info
WHERE (((tbl_Info.[Date Rcvd]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Date_Rcvd]) AND ((tbl_Info.Status)
Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].[Form]!
[txt_Status]) AND ((tbl_Info.Outlet) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_Outlet_#]) AND
((tbl_Info.[Corporate Name]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Corp_Name]) AND ((tbl_Info.[DBA/Trade
Name]) Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].
[Form]!
[cbo_DBA_Trade]) AND ((tbl_Info.[Business Unit]) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_BU]) AND
((tbl_Info.Identifier) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Id]));

The table is larger than I would have liked, but I was tasked with
amending another persons table creation rather than creating a new
database from scratch. I thought of using a recordset search but I
don't know if I can specify a particular item in a string doing things
that way. Any help will be greatly appreciated.
 
A

Allen Browne

Use = instead of Like.
The # will then be treated as a literal instead of a wildcard.

If you want to design a query to find the # in a field, use square brackets
around the # in the Criteria row of your query, e.g.:
Like "*[#]*"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table tbl_Credit_Application_Input that uses text values for
Corporate Name and a DBA/Trade Name columns. Users have a tendency to
input a name like Subway #34545 in one of the columns. When I use my
search query to try to pull the row that includes the Subway #34545 as
part of the criteria, the search comes up blank because of the #
sign. I am looking for a way to identify any record that has the #
sign in it and clean out the # sign so the record can be pulled by the
query. I pull the initial data from tbl_Credit_Application_Input
using an append query into tbl_Info, then use tbl_Info as a temp table
to pull the data into tbl_ID for the final edit screen to view. The
tbl_Credit_Application_Input to tbl_Info query works fine, but getting
the data from tbl_Info to tbl_ID is an issue when the # sign is
involved. The code for the tbl_Info to tbl_ID append query is below:

INSERT INTO tbl_ID ( [Date Rcvd], [Date Completed], Status, Outlet,
[Existing Outlet], [Type of Request], [Corporate Name], [DBA/Trade
Name], [A/P Contact Name], [A/P Phone], [A/P Fax], [Billing Address],
[Billing City], [Billing State], [Billing Zip], [Ship To Address],
[Ship To City], [Ship To State], [Ship To Zip], [Bank Name],
Attention, [Bank Phone], [Bank Fax], [Bank Acct], [T&S Signor],
[Personal Guaranty], [Corporate Record Status], [Date of
Incorporation], [Business Unit], [Reason Declined], [Additional
Decline Reason], [Sales Center], Terms, [Credit Limit], [SAP NOTES/
HEAD OFFICE INFORMATION], [CRI Comments], Selection, Reprocessed,
[Incomplete Reasons], [Cycle Time], [Date Faxed to Bank], [Date Letter
Sent], Defect1, Defect2, Defect3, Defect4, Defect5, Seniors, [Senior
Phone], [Senior Comments], RecordCtrLocNumb, Created_By, Created_Date,
Edited_By, Edited_Date, Worked_By, Change_Worked_By, User_Status_Chg,
Date_Status_Chg, Printed, Printed_Date, Identifier,
Bank_Ref_First_Request, [Profit Center], Profit_Center_Name )
SELECT tbl_Info.[Date Rcvd], tbl_Info.[Date Completed],
tbl_Info.Status, tbl_Info.Outlet, tbl_Info.[Existing Outlet],
tbl_Info.
[Type of Request], tbl_Info.[Corporate Name], tbl_Info.[DBA/Trade
Name], tbl_Info.[A/P Contact Name], tbl_Info.[A/P Phone], tbl_Info.[A/
P Fax], tbl_Info.[Billing Address], tbl_Info.[Billing City], tbl_Info.
[Billing State], tbl_Info.[Billing Zip], tbl_Info.[Ship To Address],
tbl_Info.[Ship To City], tbl_Info.[Ship To State], tbl_Info.[Ship To
Zip], tbl_Info.[Bank Name], tbl_Info.Attention, tbl_Info.[Bank Phone],
tbl_Info.[Bank Fax], tbl_Info.[Bank Acct], tbl_Info.[T&S Signor],
tbl_Info.[Personal Guaranty], tbl_Info.[Corporate Record Status],
tbl_Info.[Date of Incorporation], tbl_Info.[Business Unit], tbl_Info.
[Reason Declined], tbl_Info.[Additional Decline Reason], tbl_Info.
[Sales Center], tbl_Info.Terms, tbl_Info.[Credit Limit], tbl_Info.[SAP
NOTES/HEAD OFFICE INFORMATION], tbl_Info.[CRI Comments],
tbl_Info.Selection, tbl_Info.Reprocessed, tbl_Info.[Incomplete
Reasons], tbl_Info.[Cycle Time], tbl_Info.[Date Faxed to Bank],
tbl_Info.[Date Letter Sent], tbl_Info.Defect1, tbl_Info.Defect2,
tbl_Info.Defect3, tbl_Info.Defect4, tbl_Info.Defect5,
tbl_Info.Seniors, tbl_Info.[Senior Phone], tbl_Info.[Senior Comments],
tbl_Info.RecordCtrLocNumb, tbl_Info.Created_By, tbl_Info.Created_Date,
tbl_Info.Edited_By, tbl_Info.Edited_Date, tbl_Info.Worked_By,
tbl_Info.Change_Worked_By, tbl_Info.User_Status_Chg,
tbl_Info.Date_Status_Chg, tbl_Info.Printed, tbl_Info.Printed_Date,
tbl_Info.Identifier, tbl_Info.Bank_Ref_First_Request, tbl_Info.[Profit
Center], tbl_Info.Profit_Center_Name
FROM tbl_Info
WHERE (((tbl_Info.[Date Rcvd]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Date_Rcvd]) AND ((tbl_Info.Status)
Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].[Form]!
[txt_Status]) AND ((tbl_Info.Outlet) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_Outlet_#]) AND
((tbl_Info.[Corporate Name]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Corp_Name]) AND ((tbl_Info.[DBA/Trade
Name]) Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].
[Form]!
[cbo_DBA_Trade]) AND ((tbl_Info.[Business Unit]) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_BU]) AND
((tbl_Info.Identifier) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Id]));

The table is larger than I would have liked, but I was tasked with
amending another persons table creation rather than creating a new
database from scratch. I thought of using a recordset search but I
don't know if I can specify a particular item in a string doing things
that way. Any help will be greatly appreciated.
Click to expand...
 
M

moosecck

Allen,

Thanks for the tip about the = sign. That does pull the data
including
the # sign.

Thanks again.

Tom Brown


Use = instead of Like.
The # will then be treated as a literal instead of a wildcard.

If you want to design a query to find the # in a field, use square brackets
around the # in the Criteria row of your query, e.g.:
    Like "*[#]*"

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




I have a table tbl_Credit_Application_Input that uses text values for
Corporate Name and a DBA/Trade Name columns.  Users have a tendency to
input a name like Subway #34545 in one of the columns.  When I use my
search query to try to pull the row that includes the Subway #34545 as
part of the criteria, the search comes up blank because of the #
sign.  I am looking for a way to identify any record that has the #
sign in it and clean out the # sign so the record can be pulled by the
query.  I pull the initial data from tbl_Credit_Application_Input
using an append query into tbl_Info, then use tbl_Info as a temp table
to pull the data into tbl_ID for the final edit screen to view. The
tbl_Credit_Application_Input to tbl_Info query works fine, but getting
the data from tbl_Info to tbl_ID is an issue when the # sign is
involved. The code for the tbl_Info to tbl_ID append query is below:
Click to expand...
INSERT INTO tbl_ID ( [Date Rcvd], [Date Completed], Status, Outlet,
[Existing Outlet], [Type of Request], [Corporate Name], [DBA/Trade
Name], [A/P Contact Name], [A/P Phone], [A/P Fax], [Billing Address],
[Billing City], [Billing State], [Billing Zip], [Ship To Address],
[Ship To City], [Ship To State], [Ship To Zip], [Bank Name],
Attention, [Bank Phone], [Bank Fax], [Bank Acct], [T&S Signor],
[Personal Guaranty], [Corporate Record Status], [Date of
Incorporation], [Business Unit], [Reason Declined], [Additional
Decline Reason], [Sales Center], Terms, [Credit Limit], [SAP NOTES/
HEAD OFFICE INFORMATION], [CRI Comments], Selection, Reprocessed,
[Incomplete Reasons], [Cycle Time], [Date Faxed to Bank], [Date Letter
Sent], Defect1, Defect2, Defect3, Defect4, Defect5, Seniors, [Senior
Phone], [Senior Comments], RecordCtrLocNumb, Created_By, Created_Date,
Edited_By, Edited_Date, Worked_By, Change_Worked_By, User_Status_Chg,
Date_Status_Chg, Printed, Printed_Date, Identifier,
Bank_Ref_First_Request, [Profit Center], Profit_Center_Name )
SELECT tbl_Info.[Date Rcvd], tbl_Info.[Date Completed],
tbl_Info.Status, tbl_Info.Outlet, tbl_Info.[Existing Outlet],
tbl_Info.
[Type of Request], tbl_Info.[Corporate Name], tbl_Info.[DBA/Trade
Name], tbl_Info.[A/P Contact Name], tbl_Info.[A/P Phone], tbl_Info.[A/
P Fax], tbl_Info.[Billing Address], tbl_Info.[Billing City], tbl_Info.
[Billing State], tbl_Info.[Billing Zip], tbl_Info.[Ship To Address],
tbl_Info.[Ship To City], tbl_Info.[Ship To State], tbl_Info.[Ship To
Zip], tbl_Info.[Bank Name], tbl_Info.Attention, tbl_Info.[Bank Phone],
tbl_Info.[Bank Fax], tbl_Info.[Bank Acct], tbl_Info.[T&S Signor],
tbl_Info.[Personal Guaranty], tbl_Info.[Corporate Record Status],
tbl_Info.[Date of Incorporation], tbl_Info.[Business Unit], tbl_Info.
[Reason Declined], tbl_Info.[Additional Decline Reason], tbl_Info.
[Sales Center], tbl_Info.Terms, tbl_Info.[Credit Limit], tbl_Info.[SAP
NOTES/HEAD OFFICE INFORMATION], tbl_Info.[CRI Comments],
tbl_Info.Selection, tbl_Info.Reprocessed, tbl_Info.[Incomplete
Reasons], tbl_Info.[Cycle Time], tbl_Info.[Date Faxed to Bank],
tbl_Info.[Date Letter Sent], tbl_Info.Defect1, tbl_Info.Defect2,
tbl_Info.Defect3, tbl_Info.Defect4, tbl_Info.Defect5,
tbl_Info.Seniors, tbl_Info.[Senior Phone], tbl_Info.[Senior Comments],
tbl_Info.RecordCtrLocNumb, tbl_Info.Created_By, tbl_Info.Created_Date,
tbl_Info.Edited_By, tbl_Info.Edited_Date, tbl_Info.Worked_By,
tbl_Info.Change_Worked_By, tbl_Info.User_Status_Chg,
tbl_Info.Date_Status_Chg, tbl_Info.Printed, tbl_Info.Printed_Date,
tbl_Info.Identifier, tbl_Info.Bank_Ref_First_Request, tbl_Info.[Profit
Center], tbl_Info.Profit_Center_Name
FROM tbl_Info
WHERE (((tbl_Info.[Date Rcvd]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Date_Rcvd]) AND ((tbl_Info.Status)
Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].[Form]!
[txt_Status]) AND ((tbl_Info.Outlet) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_Outlet_#]) AND
((tbl_Info.[Corporate Name]) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Corp_Name]) AND ((tbl_Info.[DBA/Trade
Name]) Like [Forms]![frm_Edit_Existing_App]![subfrm_Choose_App].
[Form]!
[cbo_DBA_Trade]) AND ((tbl_Info.[Business Unit]) Like [Forms]!
[frm_Edit_Existing_App]![subfrm_Choose_App].[Form]![txt_BU]) AND
((tbl_Info.Identifier) Like [Forms]![frm_Edit_Existing_App]!
[subfrm_Choose_App].[Form]![txt_Id]));
Click to expand...
The table is larger than I would have liked, but I was tasked with
amending another persons table creation rather than creating a new
database from scratch. I thought of using a recordset search but I
don't know if I can specify a particular item in a string doing things
that way.  Any help will be greatly appreciated.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding a # sign in a recordset 1
Hide and move up a textbox based on previous textbox 2
Explained: Re-shipment scams (work at home) and what they do when theyhave your credit-card info 0
http://drpcdr.ca 4
FWT Newsletter - Weekly - September 13, 2004 1
FWT Newsletter - Weekly - October 4, 2004 2

Top