Convert Input Box String To "Yes/No" Value In A Query
Jan 12, 2007
I'm trying to create an update query that prompts the user to enter the "Update to" value. The field I am trying to update is a "Yes/No" check box field. When using the following code it because I don't have quotes around the default value "Yes" it inserts "-1" so it works, however the users of the database don't/won't know that "-1" is a yes value and "0" is a no value, so I'd prefer that they could type in text as "yes" or "no" instead:
Eval("InputBox(""Do you want to Complete Update Check Box?"",""Updates Completed?"",Yes,4000,2000)")
Is there a way to convert the "Yes" entered into the Input Box by the user to a "-1" or "0" value behind the scenes so that it will update the field in my table?
I have a string value that comes out of a query in the form of
01012015 02012015 12012014
etc...
where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.How do I convert these values to a valid date that can be queried on?
I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE") sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;" DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
I need to create a field in an input form that is simply the concatenation of two other text fields. I have tried all sorts of things, but when I look at the data in the table that field.
I have a field called ID that I want to be created like this:
=Format([UniqueID],"00000") & "-" & [Mosque]
This works well in my output fields, but does not work the same way on the input form. It needs to be based on the currently input values from the current record. Anyone have any ideas?
I have a field on my Report called Revenue. It is used in a calculation and there is a calculated field called Profit. Revenue-Cost=Profit. That all works.
The only thing that is wrong is that the Revenue field displays its numbers like this:
0.7 12.43 4.3 0
etc.
i.e. not formatted for currency. But I don't get the currency option under Format. It is based on a Query and this Query is based on a Linked Table.
Any suggestions on how to get it formatted as currency while still having the calculated field working?
The dates in my table are strings that appear as 12305 (1/23/05) and so forth. So some are 5 and some are 6 digits depending on the 1 or 2 digit month. I want to convert them in my query to a date field. I think I have to use the DateSerial and extract the parts of the date, but that leading 0 that is not there is throwing me off. Any easy solutions?
I have a field in my database which captures either single digit numbers or comments in text format. I want to be able to count the numbers but obviously I've had to use a memo field in order to capture both numbers and text.
The only way I can think of is to take the field and look for single character responses, then convert these into a number field so that it can be counted.
I am trying to create an order ID field based on the 1st 3 letters of the name of the buyer and the date of purchase. For example, if the buyer is James, I want the order ID to become JAM010112 (If the date is 01/01/12). How do I go about doing this?
I have a field containing data in this format 01Apr04:12:34:56 for example
It is not recognized as a date by Access, so it is stored as text
I need to put it in any general date/time format, like dd/mm/yy hh:nn:ss I also need that Access actually reads it as a date/time (not a string) to be able to merge with other data, then sort chronogically.
How can I do that (please no code, only query if possible, i'm really a newbie...)
In my query I want to extract the last 10 characters of a string in a column which represent a date in the format DD.MM.YY and then convert these to a real date format to be available for further processing.
My query looks like this:
SELECT Angebotskopf.[Laufende Nummer], Angebotskopf.Angebotsnummer, Angebotskopf.Angebotsdatum, Angebotskopf.Anfragedatum, Angebotskopf.Kunde, Angebotskopf.Ansprechpartner, Angebotskopf.Telefonnummer, Angebotskopf.Faxnummer, Angebotskopf.Projekt, Angebotskopf.Preis, CONVERT(varchar(10), RIGHT(Angebotskopf.Projekt, 8),104) AS TestAngebot FROM Angebotskopf;
But Access gives an error message "unknown function 'CONVERT'"
The "RIGHT" functions works but the resulting column is not being recognized as a date, it is a only a string and therefore useless for processing of any date related calculation.
I have two tables Atable and BTable and both have a field called AcctNo. In Atable, it is type of Number and in BTable, it is type of Text. When I do a join with these two tables
select * from Atable, BTable where Atable.AcctNo=Btable.AcctNo
I get error saying "type mismatch".
My question here is: How can I convert a type from number to string or string to number in my sql statement?
I have a form that takes input to the first, indexed, field from a barcode reader (and keyboard at a pinch). The data coming in has always been numeric so I have several checks in place to ensure it is numberic (field specification) and also that it lays in a predefined range. All works well! I've now been asked to enable this to work with a single letter prefix e.g. T1234567.
As there are many forms which use this same field I'd like to keep it numeric and as the prefix won't change I can add it later using a query. Is there a way to programatically strip the first letter from the string and use CLng() to convert to numeric using the on entry Event Procedure? All my other validation is here but I can't see a way to do this. Failing that, can I use an unbound text box to take the entry and set the index field?
I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.
This is the formula that I am using in my query:
PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100
I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code: Private Sub Command11_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
I have a (crosstab) query that summarizes information in the way that I need for a module that I have written. However, I've noticed that the each time the module tries to pull a value from the query (every step of a Do loop) it takes seconds, compared to a "real-time" pull from a table. I have had a program running for about 2 days now, whereas a similar program that pulled data from only tables took only 15 minutes. I don't forsee needing the data in its original form.
How do I convert a query to a table? (Export the query to Excel and then import.)
someone wrote this query for me in SQL and there is one part of it I don't understand can someone tell me how to convert this line for access? I don't know what || is
SELECT WE.mth_welfare, WE.id_case_welfare, WE.id_case, OB.id_member, SUM(WE.amt_mtd_assist_expnd), SUM(WE.amt_ltd_assist_expnd), SUM(WE.amt_ltd_assist_recoup), TO_NUMBER(SUBSTR(TO_CHAR(WE.mth_welfare),5,2) || SUBSTR(TO_CHAR(WE.mth_welfare),1,4)) an_mth_welfare FROM noldba.welfare_balance_by_obligation WE, noldba.obligation OB WHERE WE.id_case = OB.id_case AND WE.seq_order = OB.seq_order AND WE.seq_obligation = OB.seq_obligation
AND WE.mth_welfare <= 200708 AND WE.dt_END_validity = '31-DEC-9999' AND WE.dt_end_validity = OB.dt_end_validity AND OB.dt_beg_obligation = (SELECT MAX(c.dt_beg_obligation) FROM noldba.obligation c WHERE c.id_case = ob.id_Case AND c.seq_order = ob.seq_order AND c.seq_obligation = ob.seq_obligation AND c.dt_end_validity = '31-DEC-9999') GROUP BY WE.id_Case, OB.id_member, WE.id_case_welfare, WE.mth_welfare ORDER BY WE.mth_welfare DESC, WE.id_case DESC, OB.id_member DESC;
